DB optimizations

[Denis de Bernardy]

1. wp_title(), line 154 of functions-post.php

$title = $wpdb->get_var("SELECT cat_name FROM $wpdb->categories WHERE category_nicename = '$category_name'");

occurs after the cats are cached.

2. WP_Query, &get_posts(), line 414 of classes.php

$qcat? = $wpdb->get_var("SELECT cat_ID FROM $wpdb->categories WHERE category_nicename = '" . $qcategory_name? . "'");

occurs after the cats are cached too.

3. update_post_caches(), line 1210 of functions.php

$comment_counts = $wpdb->get_results("SELECT ID, COUNT( comment_ID ) AS ccount

FROM $wpdb->posts
LEFT JOIN $wpdb->comments ON ( comment_post_ID = ID AND comment_approved = '1')
WHERE post_status = 'publish' AND ID IN ($post_id_list)
GROUP BY ID");

can probably be merged with the main query with no serious difficulty

4. update_category_cache() in functions.php

function update_category_cache() {

global $cache_categories, $wpdb;
$dogs = $wpdb->get_results("SELECT * FROM $wpdb->categories");
foreach ($dogs as $catt) {

$catt->category_id = $catt->cat_ID; Alias.
$cache_categories[$catt->cat_ID] = $catt;

}

}

the following:

function update_category_cache() {

global $cache_categories, $wpdb;
$dogs = $wpdb->get_results("SELECT *, cat_ID as category_id FROM $wpdb->categories");
foreach ($dogs as $catt) {

$cache_categories[$catt->cat_ID] = $catt;

}

}

will likely produce less overhead

[matt]

4 applied. 3 we deliberately separate out because a separate query is faster. For 1 and 2 we need a function to loop over the categories and get an ID from a category slug.

[ryan]

Currently, we need both cat_ID and category_id in the cache or things will break. I'd rather get rid of "as category_id" and fix the few places that use category_id to use cat_ID instead. Actually, for 1.5.1 I would rather just revert.

edited on: 04-19-05 16:16

[Denis de Bernardy]

er, cat_ID and category_id will be in the cache, with 4. cat_ID is fetched twice, which produces close to zero overhead in sql. but i guess do as you see fit. i'm OK with everything as long as the cache is around for me to use in my plugins. ;)

speaking of plugins, there's this line in wp-blog-header.php

update_category_cache();

by tracing the queries I found it occurs after the 'init' hook, during which a plugin may potentially set the category cache.

Replacing with:

if ( !isset($cache_categories) )

update_category_cache();

could save a few cycles too. :)

[ryan]

Oops. Right you are. I misread. We need to do this in get_category() too then.

[matt]

Almost done with cleanup.

[Nazgul]

Closing as fixed.

Most mentioned fixes went in and the other parts of the system affected have seen too much changes since 1.5.1 for this ticket to be of any other use.