queries using indexes on varchar with sub_parts are extremely slow

[Valera]

Hello
I've noticed slow queries after upgrade from percona server 5.5 to percona 5.6 server. I have run little investigation, please see details below:
Here is my table:
mysql> describe wp_posts;
+-----------------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------------------+----------------+
| ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| post_author | bigint(20) unsigned | NO | MUL | 0 | |
| post_date | datetime | NO | MUL | 0000-00-00 00:00:00 | |
| post_date_gmt | datetime | NO | | 0000-00-00 00:00:00 | |
| post_content | longtext | NO | | NULL | |
| post_title | text | NO | | NULL | |
| post_category | int(4) | NO | | 0 | |
| post_excerpt | text | NO | | NULL | |
| post_status | varchar(20) | NO | MUL | publish | |
| comment_status | varchar(20) | NO | | open | |
| ping_status | varchar(20) | NO | | open | |
| post_password | varchar(20) | NO | | | |
| post_name | varchar(200) | NO | MUL | | |
| to_ping | text | NO | | NULL | |
| pinged | text | NO | | NULL | |
| post_modified | datetime | NO | | 0000-00-00 00:00:00 | |
| post_modified_gmt | datetime | NO | | 0000-00-00 00:00:00 | |
| post_content_filtered | longtext | NO | | NULL | |
| post_parent | bigint(20) unsigned | NO | MUL | 0 | |
| guid | varchar(255) | NO | | | |
| menu_order | int(11) | NO | | 0 | |
| post_type | varchar(20) | NO | MUL | post | |
| post_mime_type | varchar(100) | NO | | | |
| comment_count | bigint(20) | NO | | 0 | |
+-----------------------+---------------------+------+-----+---------------------+----------------+
24 rows in set (0.00 sec)

number of records in the table
mysql> select count(*) from wp_posts;
+----------+
| count(*) |
+----------+
| 190128 |
+----------+
1 row in set (0.06 sec)

Here is the query in queston, it takes around 20 seconds to complete:
mysql> SELECT p.ID FROM wp_posts AS p WHERE p.post_date < '2011-07-15 16:29:27' AND p.post_type = 'post' AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1;
+-------+
| ID |
+-------+
| 67267 |
+-------+
1 row in set (18.98 sec)

Here is explain extended output for that query:
mysql> explain extended SELECT p.ID FROM wp_posts AS p WHERE p.post_date < '2011-07-15 16:29:27' AND p.post_type = 'post' AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: ref
possible_keys: post_status,post_date,post_status_password_date_id,post_status_date_gmt,post_status_name_password,ps_pt_pp_pm,type_status_date
          key: type_status_date
      key_len: 25
          ref: const,const
         rows: 53282
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

I have following indexes created on wp_posts table:
mysql> show index in wp_posts;
+----------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_posts | 0 | PRIMARY | 1 | ID | A | 123974 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_status | 1 | post_status | A | 8 | 1 | NULL | | BTREE | | |
| wp_posts | 1 | post_date | 1 | post_date | A | 123974 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_status_password_date_id | 1 | post_status | A | 8 | 1 | NULL | | BTREE | | |
| wp_posts | 1 | post_status_password_date_id | 2 | post_password | A | 8 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_status_password_date_id | 3 | post_date | A | 123974 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_status_password_date_id | 4 | ID | A | 123974 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_status_date_gmt | 1 | post_status | A | 8 | 1 | NULL | | BTREE | | |
| wp_posts | 1 | post_status_date_gmt | 2 | post_date_gmt | A | 123974 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_status_name_password | 1 | post_status | A | 12 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_status_name_password | 2 | post_password | A | 12 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_status_name_password | 3 | post_type | A | 20 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_status_name_password | 4 | post_modified | A | 123974 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_parent | 1 | post_parent | A | 123974 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_author | 1 | post_author | A | 742 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | ps_pt_pp_pm | 1 | post_status | A | 12 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | ps_pt_pp_pm | 2 | post_type | A | 20 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | ps_pt_pp_pm | 3 | post_password | A | 20 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | ps_pt_pp_pm | 4 | post_modified | A | 123974 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_name | 1 | post_name | A | 123974 | 191 | NULL | | BTREE | | |
| wp_posts | 1 | type_status_date | 1 | post_type | A | 6 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | type_status_date | 2 | post_status | A | 16 | 1 | NULL | | BTREE | | |
| wp_posts | 1 | type_status_date | 3 | post_date | A | 123974 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | type_status_date | 4 | post_password | A | 123974 | NULL | NULL | | BTREE | | |
+----------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
24 rows in set (0.00 sec)

As you can see from the output of explain extended index type_status_date is being used for the query in question, on percona 5.5 this query was completing under 0.01 seconds.
If I drop that index and recreate it without using sub_part, query runs with a speed comparable to percona 5.5., please see below:

mysql> drop index type_status_date on wp_posts;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index type_status_date on wp_posts(post_type,post_status,post_date,post_password);
Query OK, 0 rows affected (1 min 7.77 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SELECT p.ID FROM wp_posts AS p WHERE p.post_date < '2011-07-15 16:29:27' AND p.post_type = 'post' AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1;
+-------+
| ID |
+-------+
| 67267 |
+-------+
1 row in set (0.01 sec)

Here is query plan for the same query with a new index:
mysql> explain extended SELECT p.ID FROM wp_posts AS p WHERE p.post_date < '2011-07-15 16:29:27' AND p.post_type = 'post' AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: range
possible_keys: post_status,post_date,post_status_password_date_id,post_status_date_gmt,post_status_name_password,ps_pt_pp_pm,type_status_date
          key: type_status_date
      key_len: 49
          ref: NULL
         rows: 51010
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)

Cardinality values with a new index did not change much:

mysql> show index in wp_posts;
+----------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_posts | 0 | PRIMARY | 1 | ID | A | 123974 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_status | 1 | post_status | A | 8 | 1 | NULL | | BTREE | | |
| wp_posts | 1 | post_date | 1 | post_date | A | 123974 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_status_password_date_id | 1 | post_status | A | 8 | 1 | NULL | | BTREE | | |
| wp_posts | 1 | post_status_password_date_id | 2 | post_password | A | 8 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_status_password_date_id | 3 | post_date | A | 123974 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_status_password_date_id | 4 | ID | A | 123974 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_status_date_gmt | 1 | post_status | A | 8 | 1 | NULL | | BTREE | | |
| wp_posts | 1 | post_status_date_gmt | 2 | post_date_gmt | A | 123974 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_status_name_password | 1 | post_status | A | 12 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_status_name_password | 2 | post_password | A | 12 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_status_name_password | 3 | post_type | A | 20 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_status_name_password | 4 | post_modified | A | 123974 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_parent | 1 | post_parent | A | 123974 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_author | 1 | post_author | A | 742 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | ps_pt_pp_pm | 1 | post_status | A | 12 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | ps_pt_pp_pm | 2 | post_type | A | 20 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | ps_pt_pp_pm | 3 | post_password | A | 20 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | ps_pt_pp_pm | 4 | post_modified | A | 123974 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | post_name | 1 | post_name | A | 123974 | 191 | NULL | | BTREE | | |
| wp_posts | 1 | type_status_date | 1 | post_type | A | 6 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | type_status_date | 2 | post_status | A | 20 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | type_status_date | 3 | post_date | A | 123974 | NULL | NULL | | BTREE | | |
| wp_posts | 1 | type_status_date | 4 | post_password | A | 123974 | NULL | NULL | | BTREE | | |
+----------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
24 rows in set (0.01 sec)

The only difference is usage of sub_part in the original index.
I can reproduce this issue reliably, by dropping modified index and re-creating original one.
Let me know if you need more details, exact version of percona software packages could be found in the attached file.

Best regards,
Valery Masiutsin

[Valera (val-masutin)]

I am attaching traces for slow and fast queries.

[Valera (val-masutin)]

Fast query trace

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-505

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-3375