[wp-trac] [WordPress Trac] #31071: post_mime_type related queries still slow on larger sites
WordPress Trac
noreply at wordpress.org
Mon Nov 30 17:32:43 UTC 2015
#31071: post_mime_type related queries still slow on larger sites
-------------------------------------------------+-------------------------
Reporter: archon810 | Owner: pento
Type: defect (bug) | Status: assigned
Priority: normal | Milestone: Future
Component: Media | Release
Severity: normal | Version: 4.1
Keywords: dev-feedback reporter-feedback 2nd- | Resolution:
opinion | Focuses:
| performance
-------------------------------------------------+-------------------------
Changes (by jamesmehorter):
* keywords: has-patch => dev-feedback reporter-feedback 2nd-opinion
Comment:
Hi everyone,
I created a new multi-column index for post_type and post_mime_type as
done by @archon810; and I can confirm A) the audio and video queries are
now blazing fast. And B) per @pento's request I also tested adding a
post_status clause in the months query; the addition of the post_status
clause does indeed ensure the query utilizes the type_status_date index
(not the new mime type index) and does not require a filesort. However,
please note that the filesort was used even before the new index was
added. That query is simply able to run without a filesort when a
post_status clause is present. Regardless of what we do with indexes that
query should be optimized to include a post_status clause and a caching
layer.
{{{
mysql Ver 14.14 Distrib 5.5.46, for debian-linux-gnu (i686) using
readline 6.2
mysql> alter table wp_14_posts add index type_mime (post_type,
post_mime_type);
mysql> show indexes from wp_14_posts;
+-------------+------------+------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name
| Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment | Index_comment |
+-------------+------------+------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_14_posts | 0 | PRIMARY | 1 | ID
| A | 2111190 | NULL | NULL | | BTREE |
| |
| wp_14_posts | 1 | type_status_date | 1 | post_type
| A | 14 | NULL | NULL | | BTREE |
| |
| wp_14_posts | 1 | type_status_date | 2 | post_status
| A | 14 | NULL | NULL | | BTREE |
| |
| wp_14_posts | 1 | type_status_date | 3 | post_date
| A | 1055595 | NULL | NULL | | BTREE |
| |
| wp_14_posts | 1 | type_status_date | 4 | ID
| A | 2111190 | NULL | NULL | | BTREE |
| |
| wp_14_posts | 1 | post_parent | 1 | post_parent
| A | 301598 | NULL | NULL | | BTREE |
| |
| wp_14_posts | 1 | post_author | 1 | post_author
| A | 14 | NULL | NULL | | BTREE |
| |
| wp_14_posts | 1 | post_name | 1 | post_name
| A | 2111190 | 191 | NULL | | BTREE |
| |
| wp_14_posts | 1 | type_mime | 1 | post_type
| A | 165 | NULL | NULL | | BTREE |
| |
| wp_14_posts | 1 | type_mime | 2 |
post_mime_type | A | 165 | NULL | NULL | |
BTREE | | |
+-------------+------------+------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> EXPLAIN SELECT DISTINCT YEAR( post_date ) AS year, MONTH( post_date
) AS month FROM wp_14_posts WHERE post_type = 'attachment' ORDER BY
post_date DESC;
+----+-------------+-------------+------+----------------------------+------------------+---------+-------+--------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra
|
+----+-------------+-------------+------+----------------------------+------------------+---------+-------+--------+-----------------------------------------------------------+
| 1 | SIMPLE | wp_14_posts | ref | type_status_date,type_mime |
type_status_date | 82 | const | 969628 | Using where; Using index;
Using temporary; Using filesort |
+----+-------------+-------------+------+----------------------------+------------------+---------+-------+--------+-----------------------------------------------------------+
mysql> EXPLAIN SELECT DISTINCT YEAR( post_date ) AS year, MONTH( post_date
) AS month FROM wp_14_posts WHERE post_type = 'attachment' AND post_status
= 'inherit' ORDER BY post_date DESC;
+----+-------------+-------------+------+----------------------------+------------------+---------+-------------+--------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra
|
+----+-------------+-------------+------+----------------------------+------------------+---------+-------------+--------+-------------------------------------------+
| 1 | SIMPLE | wp_14_posts | ref | type_status_date,type_mime |
type_status_date | 164 | const,const | 969628 | Using where; Using
index; Using temporary |
+----+-------------+-------------+------+----------------------------+------------------+---------+-------------+--------+-------------------------------------------+
}}}
That said, I think this may be the wrong direction for this ticket.. hear
me out :)
Our current index setup is not intuitive or performant. We've got a mix of
single-column indexes and multi-column indexes, which do not include some
commonly used columns—ideally we should have an index for any column used
in a where or order by statement.
As shown with the issue above (mysql using the wrong index), we're relying
on mysql's index merge optimization to choose which indexes it can use
instead of structuring our posts table in a way that instructs mysql.
This may be slightly radical.. but what if we considered replacing all of
our current indexes with one, single multi-column index. I've tested this
structure, and it speeds up the queries mentioned here (including the
months query).. and probably many more. Doing so would requires no changes
to any queries as @pento pointed out (though these should still be
adjusted to include a post_status clause as mentioned above). And using a
single multi-column index has been proven to be quicker than multiple
indexes (https://www.percona.com/blog/2014/01/03/multiple-column-index-vs-
multiple-indexes-with-mysql-56/).
The only downside I can see is the time it would take to build the new
index during an update routine. I clocked the index creation on a table
with 2.2m posts at 1min (code/output below). And during that time reads
and writes are blocked by default. However, since mysql 5.6 the add index
command can be instructed to operate asynchronously, allowing reads and
writes while the index is created with the algorithm and lock arguments
(http://stackoverflow.com/a/21842589/655837).
Thoughts? Once we agree on an approach I'm happy to help supply some
patches.
{{{
mysql> alter table wp_14_posts add index posts (ID, post_author,
post_date, post_status, comment_status, ping_status, post_name,
post_modified, post_parent, menu_order, post_type, post_mime_type,
comment_count);
Query OK, 0 rows affected, 2 warnings (1 min 1.33 sec)
Records: 0 Duplicates: 0 Warnings: 2
mysql> show indexes from wp_14_posts;
+-------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment
| Index_comment |
+-------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_14_posts | 0 | PRIMARY | 1 | ID | A
| 1697812 | NULL | NULL | | BTREE | |
|
| wp_14_posts | 1 | posts | 1 | ID | A
| 186 | NULL | NULL | | BTREE | |
|
| wp_14_posts | 1 | posts | 2 | post_author | A
| 186 | NULL | NULL | | BTREE | |
|
| wp_14_posts | 1 | posts | 3 | post_date | A
| 186 | NULL | NULL | | BTREE | |
|
| wp_14_posts | 1 | posts | 4 | post_status | A
| 186 | NULL | NULL | | BTREE | |
|
| wp_14_posts | 1 | posts | 5 | comment_status | A
| 186 | NULL | NULL | | BTREE | |
|
| wp_14_posts | 1 | posts | 6 | ping_status | A
| 186 | NULL | NULL | | BTREE | |
|
| wp_14_posts | 1 | posts | 7 | post_name | A
| 186 | 191 | NULL | | BTREE | |
|
| wp_14_posts | 1 | posts | 8 | post_modified | A
| 186 | NULL | NULL | | BTREE | |
|
| wp_14_posts | 1 | posts | 9 | post_parent | A
| 186 | NULL | NULL | | BTREE | |
|
| wp_14_posts | 1 | posts | 10 | menu_order | A
| 186 | NULL | NULL | | BTREE | |
|
| wp_14_posts | 1 | posts | 11 | post_type | A
| 186 | NULL | NULL | | BTREE | |
|
| wp_14_posts | 1 | posts | 12 | post_mime_type | A
| 186 | NULL | NULL | | BTREE | |
|
| wp_14_posts | 1 | posts | 13 | comment_count | A
| 186 | NULL | NULL | | BTREE | |
|
+-------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
}}}
--
Ticket URL: <https://core.trac.wordpress.org/ticket/31071#comment:26>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform
More information about the wp-trac
mailing list