WordPress.org

Support

Support » How-To and Troubleshooting » Optimizing SQL query for metadata

Optimizing SQL query for metadata

Viewing 2 replies - 1 through 2 (of 2 total)
  • That’s just bad code it seems. Though I’m sure that’s one of the largest WP blogs at the moment. 😉 You’ll probably be very helpful in trying to hunt down such issues.

    I’m not sure what that query is for… given the LIMIT 10, which is odd. My guess is it is trying to pre-populate up to the first 10 potential meta field names for the edit form, but should be caching those as a field in the options table or something.

    -d

    Ok, I did some poking around and optimized some really horrid SQL query and got it to work I THINK the way they intended.

    It looks like the intention of the original query was to give you a select box of the 10 last used meta keys in the db. Unfortunately, it’s REALLLLLLLLYYYYY slow when you have piles of metadata.

    The offending function for editing is in admin-functions.php and is meta_form().

    If all you’re looking for is the last 10 keys, the following code works just fine:


    $distinctkeys = $wpdb->get_col ("
    SELECT DISTINCT meta_id
    FROM $wpdb->postmeta
    ORDER BY meta_id DESC LIMIT 10");

    $sql = "SELECT meta_key FROM $wpdb->postmeta WHERE meta_id IN (" . join(",", $distinctkeys) . ")";
    $keys = $wpdb->get_col($sql);

    do a SELECT with a DISTINCT and LIMIT to give you the meta_id’s of the last 10, then do a select of the key names from that set of id’s. It could be done in one SQL query using sub-selects and the IN clause, but that only works for MySQL 4.1 and above, so it isn’t portable (really).

    Looking at get_meta_keys in the same file shows the same ugly SQL query style, so I optimized it to:


    $keys = $wpdb->get_col ("SELECT DISTINCT meta_key FROM $wpdb->postmeta");

    return $keys;

    This again should run a lot faster against large metakey tables.

    The diff for admin-functions.php (against a stock 1.5.1.1 install) can be found here if anyone wants to try it. My thoughts to the WP team are to look at incorporating it in to the next update if it doesn’t break anything else.

    As I go through this project, I’ll keep optimizing for speed and posting.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Optimizing SQL query for metadata’ is closed to new replies.
Skip to toolbar