Order by meta_key where value is serialized WordPress

plovs answers:

Having your tweets meta info serialized is not a good idea. But you really are going to lose the ability to query your data in any efficient manner when serializing entries into the WP database.

The overall performance saving and gain you think you are achieving by serialization is not going to be noticeable to any major extent. You might obtain a slightly smaller database size but the cost of SQL transactions is going to be heavy if you ever query those fields and try to compare them in any useful, meaningful manner.

Instead, save serialization for data that you do not intend to query in that nature, but instead would only access in a passive fashion by the direct WP API call get_post_meta() - from that function you can unpack a serialized entry to access its array properties too.

I recommend you unserialise your data and modify your save routine. Something similar to this should convert your data to the new format:


if (have_posts()) :
function filter_where($where = '') {
global $wpdb;
$where .= " AND post_date > '" . date('Y-m-d', strtotime('-1 day')) . "'";
return $where;
}
add_filter('posts_where', 'filter_where');

//New loop to cleanup the db, this should be added to the code that fetches your tweets
//it will clean up your db, if here is nothing to clean, it will just continue
$args = array(
'meta_key' => 'tweets_count',
'posts_per_page' => -1
);

$query = new WP_Query( $args );
if($query->have_posts()){
while($query->have_posts()){
$query->the_post();
$oldserial = get_post_meta($post->id,'tweets_count',true);
add_post_meta($post->ID,'_tweets_count',$oldserial['count']);
add_post_meta($post->ID,'_timeout',$oldserial['timeout']);
delete_post_meta($post->ID,'tweets_count',$oldserial);
}
}
//end new loop

$args=array(
'meta_key' => '_tweets_count',
'orderby' => 'meta_value',
'showposts' => 10,
);
query_posts($args);
while (have_posts()) : the_post();
include ( TEMPLATEPATH . '/loop.php' );
endwhile;
endif;


Now it will work again as before.

If you want to speed things up, you might use:

- [[LINK href="http://codex.wordpress.org/Transients_API"]]codex.wordpress.org/Transients_API[[/LINK]]
- [[LINK href="http://www.paulund.co.uk/cache-data-with-wordpress"]]www.paulund.co.uk/cache-data-with-wordpress[[/LINK]]

---