Key changes slowed down postmeta lookup

  • ResolvedPlugin Author OllieJones

    (@olliejones)


    4 years, 9 months ago

    A user with 100K products in a WooCommerce database reported a slowdown in this query after the “high-speed” keys were put in place. The query slowed from 3.5s to 12s. 

    SELECT post_id 
  FROM wp_postmeta
 WHERE meta_key = '_wp_attached_file'
   AND meta_value LIKE 'big1.jpg'
 ORDER BY meta_id ASC 
 LIMIT 1;

    This query, in the user’s large installation, scans the key starting with meta_id. It takes longer with the plugin because the meta_id key is nonclustered in that case.

    The query takes too long (with or without new keys).

    • This topic was modified 4 years, 9 months ago by OllieJones.
Viewing 3 replies - 1 through 3 (of 3 total)
  • Plugin Author rjasdfiii

    (@rjasdfiii)

    4 years, 5 months ago

    Can you get EXPLAINs both before and after the ALTER?

    The following Optimizer “bugs” are in play here:

    * Preferring an index for the ORDER BY instead of for the WHERE clause.
    * Index prefix (meta_value(191) handling is wimpy.
    * Failure to take LIMIT into account.

    Note: Different versions of MySQL and MariaDB do a better/worse job on these “bugs”. What version are you using?

    Back to the specific question and to provide a workaround.

    I suggest that there is no “business logic” reason for ORDER BY meta_id ASC. Leave it off.

    Plugin Author OllieJones

    (@olliejones)

    4 years, 5 months ago

    I put this in here to remind myself to change the so-called “high speed index” in the next release of the subject plugin. The present “high-speed index” is defined, for MySQL / MariaDB versions supporting the Barracuda version of InnoDB, as

    ALTER TABLE wp_postmeta ADD KEY meta_key (meta_key, post_id)

    and it needs to be

    ALTER TABLE wp_postmeta ADD KEY meta_key (meta_key, meta_value(64), post_id);

    https://github.com/OllieJones/index-wp-mysql-for-speeed/issues/2

    As a plugin writer I don’t have any control over the queries in WordPress. Your observation about the usefulness of the ORDER BY is, I believe, correct.

    Plugin Author OllieJones

    (@olliejones)

    4 years, 5 months ago

    Hi, Rick, I didn’t realize this was you.

Viewing 3 replies - 1 through 3 (of 3 total)

The topic ‘Key changes slowed down postmeta lookup’ is closed to new replies.