Slower looks up with meta_id sort from update_meta_cache

  • I’m trying to speed up slow queries from the update_meta_cache function in WP which sorts postmeta post id looks up by the meta_id column, as below:

    SELECT post_id, meta_key, meta_value
    FROM wp_postmeta
    WHERE post_id IN (100,….)
    ORDER BY meta_id ASC;

    Adding the indexes in this plugin seems to add an additional 90ms on large look ups (when requesting hundreds of IDs) than the standard indexes.

    Do you have any suggestions on potential indexes that could be added to speed up sorting by meta_id? I’m guessing because the meta_id isn’t in the original select, it results in an extra order by filesort which is much slower?

Viewing 2 replies - 1 through 2 (of 2 total)
  • Plugin Author OllieJones

    (@olliejones)

    Thanks for the question. Some versions of MySQL / MariaDB perform the IN (long, list, of, id, values) operation absurdly slowly.

    It would help troubleshooting if you uploaded your database metadata and a monitor showing the problem.

    Thread Starter Antony Booker

    (@antonynz)

    Thanks for the reply.

    The bottleneck appears to be when “ORDER BY meta_id ASC;” is in the query with the high performance keys which results in a high creating sort time.

    Here is a profile of the query with high performance indexes on the postmeta table taking .13s:

    Duration: 0.13756471s

    Starting: 0.000143s
    checking permissions: 0.000006s
    Opening tables: 0.000009s
    After opening tables: 0.000007s
    System lock: 0.000004s
    table lock: 0.000005s
    init: 0.000080s
    Optimizing: 0.000033s
    Statistics: 0.000248s
    Preparing: 0.000017s
    Sorting result: 0.000006s
    Executing: 0.000004s
    Sending data: 0.000006s
    Creating sort index: 0.136885s
    End of update loop: 0.000020s
    Query end: 0.000004s
    Commit: 0.000004s
    Query end: 0.000004s
    closing tables: 0.000004s
    Unlocking tables: 0.000004s
    closing tables: 0.000005s
    Query end: 0.000005s
    Starting cleanup: 0.000004s
    Freeing items: 0.000016s
    Updating status: 0.000013s
    Logging slow query: 0.000024s
    Reset for next command: 0.000006s
    ALTER TABLE wp_postmeta
      ADD PRIMARY KEY (post_id,meta_key,meta_id),
      ADD UNIQUE KEY meta_id (meta_id),
      ADD KEY meta_key (meta_key,meta_value(32),post_id,meta_id),
      ADD KEY meta_value (meta_value(32),meta_id);
    Postmeta rows: 2,019,801 461.4 MiB

    And a profile with the standard indexes taking .05s

    Query ID: 1 Duration: 0.05160964s

    Starting: 0.000139s
    checking permissions: 0.000006s
    Opening tables: 0.000009s
    After opening tables: 0.000006s
    System lock: 0.000005s
    table lock: 0.000005s
    init: 0.000084s
    Optimizing: 0.000035s
    Statistics: 0.000251s
    Preparing: 0.000027s
    Sorting result: 0.000006s
    Executing: 0.000004s
    Sending data: 0.000006s
    Creating sort index: 0.050946s
    End of update loop: 0.000019s
    Query end: 0.000005s
    Commit: 0.000004s
    Query end: 0.000004s
    closing tables: 0.000004s
    Unlocking tables: 0.000004s
    closing tables: 0.000004s
    Query end: 0.000005s
    Starting cleanup: 0.000004s
    Freeing items: 0.000015s
    Updating status: 0.000009s
    Reset for next command: 0.000006s
    ALTER TABLE wp_postmeta
      ADD PRIMARY KEY (meta_id),
      ADD KEY post_id (post_id),
      ADD KEY meta_key (meta_key(191));
    Postmeta rows: 1,759,255 294.0 MiB

    Could the longer times simply be from the larger database size with the extra indexes?

Viewing 2 replies - 1 through 2 (of 2 total)
  • You must be logged in to reply to this topic.