2

I work on WordPress plugin which implements sitemap functionality.

One of the steps is to build an index of sitemap pages for post entries. The logic required is roughly: determine last modified time for every block of N posts.

For N=1000 the resulting query generated and executed looks like this (this is ancient legacy, the development history for which is long lost):

SELECT post_modified_gmt FROM (
    SELECT @rownum:=@rownum+1 rownum, wp_posts.post_modified_gmt 
    FROM (SELECT @rownum:=0) r, wp_posts
        WHERE post_status IN ('publish','inherit')
        AND post_type = 'test'
        ORDER BY post_modified_gmt ASC
    ) x 
WHERE rownum %1000 = 0

This works reasonably on smaller sites, but starts to get slow on larger sites (100Ks of posts) and impractically slow on very large sites (1M+ of posts).

I am not very proficient with SQL, so far everything I tried boils down to that query needs to go through every single row, which doesn't scale.

Trying to do multiple queries instead didn't go too well, since offsets don't scale that well either, so doing 20 simpler queries on 1M posts will add up to even worse results.

The only peculiar optimization I stumbled upon so far is hinting to not use available index, which is counter-intuitive, but does speed up query some:

SELECT post_modified_gmt FROM (
    SELECT @rownum:=@rownum+1 rownum, wp_posts.post_modified_gmt 
    FROM (SELECT @rownum:=0) r, wp_posts USE INDEX()
        WHERE post_status IN ('publish','inherit')
        AND post_type = 'test'
        ORDER BY post_modified_gmt ASC
    ) x 
WHERE rownum %1000 = 0

Which other approaches or techniques could I look into to optimize such use case?

PS looking for something that would preferably work on wide range of MySQL version (5.0+), but even something that could be progressive enhancement on newer version (5.6+) would be grand.

EXPLAIN for the query (the version with index):

id  select_type     table       type    possible_keys       key                 key_len ref     rows    Extra
1   PRIMARY         <derived2>  ALL     null                null                null    null    15484   Using where
2   DERIVED         <derived3>  system  null                null                null    null    1       Using filesort
2   DERIVED         wp_posts    ref     type_status_date    type_status_date    82      const   15484   Using index condition; Using where
3   DERIVED         null        null    null                null                null    null    null    No tables used

Table CREATE statement:

CREATE TABLE `wp_posts` (
    `ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `post_author` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    `post_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    `post_date_gmt` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    `post_content` LONGTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `post_title` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `post_excerpt` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `post_status` VARCHAR(20) NOT NULL DEFAULT 'publish' COLLATE 'utf8mb4_unicode_ci',
    `comment_status` VARCHAR(20) NOT NULL DEFAULT 'open' COLLATE 'utf8mb4_unicode_ci',
    `ping_status` VARCHAR(20) NOT NULL DEFAULT 'open' COLLATE 'utf8mb4_unicode_ci',
    `post_password` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
    `post_name` VARCHAR(200) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
    `to_ping` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `pinged` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `post_modified` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    `post_modified_gmt` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    `post_content_filtered` LONGTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `post_parent` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    `guid` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
    `menu_order` INT(11) NOT NULL DEFAULT '0',
    `post_type` VARCHAR(20) NOT NULL DEFAULT 'post' COLLATE 'utf8mb4_unicode_ci',
    `post_mime_type` VARCHAR(100) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
    `comment_count` BIGINT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY (`ID`),
    INDEX `type_status_date` (`post_type`, `post_status`, `post_date`, `ID`),
    INDEX `post_parent` (`post_parent`),
    INDEX `post_author` (`post_author`),
    INDEX `post_name` (`post_name`(191))
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=MyISAM
AUTO_INCREMENT=19833;

The performance difference between query versions:

SELECT rownum, post_modified_gmt FROM (
    SELECT @rownum:=@rownum+1 rownum, wp_posts.post_modified_gmt 
    FROM (SELECT @rownum:=0) r, wp_posts
        WHERE post_status IN ('publish','inherit')
        AND post_type = 'test'
        ORDER BY post_modified_gmt ASC
    ) x 
WHERE rownum %1000 = 0;
/* Affected rows: 0  Found rows: 15  Warnings: 0  Duration for 1 query: 0,218 sec. */

SELECT rownum, post_modified_gmt FROM (
    SELECT @rownum:=@rownum+1 rownum, wp_posts.post_modified_gmt 
    FROM (SELECT @rownum:=0) r, wp_posts USE INDEX()
        WHERE post_status IN ('publish','inherit')
        AND post_type = 'test'
        ORDER BY post_modified_gmt ASC
    ) x 
WHERE rownum %1000 = 0;
/* Affected rows: 0  Found rows: 15  Warnings: 0  Duration for 1 query: 0,046 sec. */
3

There seems to be a bug in MySQL (checked up to 5.6.21 on sqlfiddle.com) where the optimizer does not identify the optimal index when it is available. It can be worked around by using force index() hint:

SELECT post_modified_gmt FROM (
    SELECT @rownum:=@rownum+1 rownum, wp_posts.post_modified_gmt 
    FROM (SELECT @rownum:=0) r, wp_posts FORCE INDEX(type_modified_status)
        WHERE post_status IN ('publish','inherit')
        AND post_type = 'test'
        ORDER BY post_modified_gmt ASC
    ) x 
WHERE rownum %1000 = 0;

where the best index is

INDEX `type_modified_status` (`post_type`, `post_modified_gmt`, `post_status`)

This index enables ref access on post_type and optimal sorting by post_modified_gmt with post_status available directly for additional checks so the query does not need the random seek into the main table data.

The index is being used even without the force index() hint, but optimizer for some reason does not recognize the availability of post_status and reads the full row from the table, slowing the query execution by lot of random IO.

http://sqlfiddle.com/#!9/1cc46/4

Edit: after some more digging, the right index is used after ANALYZE TABLE wp_posts; without the index hint too. But from what I gathered ANALYZE is not a proper solution for your case.

| improve this answer | |
  • Curiously just adding this index makes performance even worse in my dev install, but FORCE on it does improve performance beyond empty index hack. I will have to ponder if this is something we can practically ship in the plugin, but from SQL perspective this seems as good as it gets for the query. Thank you for your time! – Rarst May 30 '16 at 15:59
  • @Rarst yes, the index is optimal as far as I can tell. The problem is MySQL for some reasons does not see it right without help. For implementation make sure the index exists or catch the error if it does not as FORCE INDEX will return error. Or try to ignore other indexes instead to see if maybe one of them is the reason. – jkavalik May 30 '16 at 16:17
1

(post_type, post_modified_gmt, post_status) is 'optimal' because it is (1) covering, and (2) ordered in perhaps the best way, and (3) the table is MyISAM, yet this index is much smaller.

MyISAM is a bad choice for a table with a lot of bulky (TEXT) columns. InnoDB would probably run faster.

MyISAM is prone to fragmentation. If there has been a lot of churn in the data, then maybe OPTIMIZE TABLE would help. (InnoDB does not have that issue.)

I think the outer query can be eliminated:

SELECT  post_modified_gmt
    FROM  ( SELECT @rownum := 0 ) init
    JOIN  wp_posts
    WHERE  post_status IN ('publish','inherit')
      AND  post_type = 'test'
      AND  (@rownum:=@rownum+1) %1000 = 0
    ORDER BY  post_modified_gmt ASC ;

The rownum clause probably needs to be last.

Keep in mind when timing things

  • Avoid the Query cache (add SQL_NO_CACHE), and
  • Run the query twice and take the second timing (to avoid other caching issues).

Another 'timing' technique is

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

This tends to show how many rows are touched.

| improve this answer | |
  • Since this is a publicly released product, unfortunately we don't have control over DB. :( Can only recommend people to try switching engine. I've tried your query suggestion, with it @rownum value seems to grow persistently between multiple query runs (it doesn't get set to 0 at the start of the query). – Rarst Jun 8 '16 at 12:29
  • My bad. I edited it to initialize @rownum. – Rick James Jun 8 '16 at 17:06
  • Thank you! This is certainly easier version to read. Unfortunately it still seems to suffer from exact same issue of not picking up index correctly, unless forced. :( – Rarst Jun 8 '16 at 17:24
  • Please try this order: INDEX(post_type, post_status, post_modified_gmt). – Rick James Jun 8 '16 at 17:30
  • I mean I can speed it up with an index, as per other answer it just doesn't pick it up itself. But! I think this version of the query benefits from type_status_date index, which should be available out of the box. That is great. – Rarst Jun 8 '16 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.