WordPress.org

Make WordPress Core

Opened 10 years ago

Closed 9 years ago

#1238 closed defect (bug) (fixed)

DB optimizations

Reported by: Denis de Bernardy Owned by:
Milestone: Priority: normal
Severity: trivial Version: 1.5.1
Component: Optimization Keywords:
Focuses: Cc:

Description

  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.

  1. 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.

  1. 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

  1. 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

Change History (7)

comment:1 @Denis de Bernardy10 years ago

  • Patch set to No

comment:2 @matt10 years ago

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.

comment:3 @ryan10 years ago

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

comment:4 @Denis de Bernardy10 years ago

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. :)

comment:5 @ryan10 years ago

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

comment:6 @matt10 years ago

Almost done with cleanup.

comment:7 @Nazgul9 years ago

  • Resolution set to fixed
  • Status changed from new to closed

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.

Note: See TracTickets for help on using tickets.