Add an additional index to `wp_postmeta`.

[rafaucau]

My web host, examining the performance of the site, suggested adding an additional index to wp_postmeta:

CREATE INDEX post_id_meta_key ON wp_postmeta (post_id, meta_key(191))

After adding this index, the site runs noticeably faster. My wp_postmeta table has 2443656 records. It would be nice if this plugin would add this index.

MariaDB [cu9eeb_dev0806]> show index from wp_postmeta;

 Table       | Non_unique | Key_name         | Seq| Column_name | Cardinality | Sub_part |
| wp_postmeta |          0 | PRIMARY          |  1 | post_id     |      544316 |     NULL |
| wp_postmeta |          0 | PRIMARY          |  2 | meta_key    |     1632949 |     NULL |
| wp_postmeta |          0 | PRIMARY          |  3 | meta_id     |     1632949 |     NULL |

| wp_postmeta |          0 | meta_id          |  1 | meta_id     |     1632949 |     NULL |

| wp_postmeta |          1 | meta_key         |  1 | meta_key    |      136079 |     NULL |
| wp_postmeta |          1 | meta_key         |  2 | meta_value  |     1632949 |       32 |
| wp_postmeta |          1 | meta_key         |  3 | post_id     |     1632949 |     NULL |
| wp_postmeta |          1 | meta_key         |  4 | meta_id     |     1632949 |     NULL |

| wp_postmeta |          1 | meta_value       |  1 | meta_value  |     1632949 |       32 |
| wp_postmeta |          1 | meta_value       |  2 | meta_id     |     1632949 |     NULL |

| wp_postmeta |          1 | post_id_meta_key |  1 | post_id     |      544316 |     NULL |
| wp_postmeta |          1 | post_id_meta_key |  2 | meta_key    |     1632949 |      191 |

[OllieJones]

Interesting observation. I'd like to know more about your setup. I'd like to see EXPLAIN output for the queries affected by this.

You must have InnoDB because you're using the composite primary key (and you have the so-called clustered index behind it -- the contents of the table). The suggested key is a subset of that primary key, and so a duplicate. Plus, it has the prefix (191) made unnecessary by the latest database software. So, I'd like to see if the query planner uses the new index.

When adding or dropping an index you get an implicit table analysis operation. Is it possible that was what made your system faster?

[rafaucau]

I'd like to see EXPLAIN output for the queries affected by this.

SQL query in two versions. With and without the index. It is difficult to measure whether it had any effect.

MariaDB [cu9eeb_dev0806]> show index from wp_postmeta;
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table       | Non_unique | Key_name   | Seq_in_index | Column_name | 
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment 
| Index_comment | Ignored |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| wp_postmeta |          0 | PRIMARY    |            1 | post_id     | A         
|      537197 |     NULL | NULL   |      | BTREE      |         |               
| NO      |
| wp_postmeta |          0 | PRIMARY    |            2 | meta_key    | A         
|     1611591 |     NULL | NULL   |      | BTREE      |         |               
| NO      |
| wp_postmeta |          0 | PRIMARY    |            3 | meta_id     | A         
|     1611591 |     NULL | NULL   |      | BTREE      |         |               
| NO      |
| wp_postmeta |          0 | meta_id    |            1 | meta_id     | A         
|     1611591 |     NULL | NULL   |      | BTREE      |         |               
| NO      |
| wp_postmeta |          1 | meta_key   |            1 | meta_key    | A         
|      123968 |     NULL | NULL   |      | BTREE      |         |               
| NO      |
| wp_postmeta |          1 | meta_key   |            2 | meta_value  | A         
|     1611591 |       32 | NULL   | YES  | BTREE      |         |               
| NO      |
| wp_postmeta |          1 | meta_key   |            3 | post_id     | A         
|     1611591 |     NULL | NULL   |      | BTREE      |         |               
| NO      |
| wp_postmeta |          1 | meta_key   |            4 | meta_id     | A         
|     1611591 |     NULL | NULL   |      | BTREE      |         |               
| NO      |
| wp_postmeta |          1 | meta_value |            1 | meta_value  | A         
|     1611591 |       32 | NULL   | YES  | BTREE      |         |               
| NO      |
| wp_postmeta |          1 | meta_value |            2 | meta_id     | A         
|     1611591 |     NULL | NULL   |      | BTREE      |         |               
| NO      |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
10 rows in set (0.001 sec)

MariaDB [cu9eeb_dev0806]> explain SELECT b.post_id, b.meta_value FROM 
`wp_posts` a LEFT JOIN `wp_postmeta` b ON b.post_id = a.ID AND b.meta_key 
= '_wp_attachment_metadata' LEFT JOIN `wp_litespeed_img_optm` c ON  
c.post_id = a.ID WHERE a.post_type = 'attachment' AND a.post_status = 
'inherit' AND a.post_mime_type IN ('image/jpeg', 'image/png', 'image/gif') 
AND c.id IS NULL ORDER BY a.ID DESC;
+------+-------------+-------+-------+------------------+---------+---------+---------------------------+--------+--------------------------------------+
| id   | select_type | table | type  | possible_keys    | key     | 
key_len | ref                       | rows   | Extra                                
|
+------+-------------+-------+-------+------------------+---------+---------+---------------------------+--------+--------------------------------------+
|    1 | SIMPLE      | a     | index | type_status_date | PRIMARY | 8       
| NULL                      | 343743 | Using where                          
|
|    1 | SIMPLE      | b     | ref   | PRIMARY,meta_key | PRIMARY | 1030    
| cu9eeb_dev0806.a.ID,const | 1      | Using where                          
|
|    1 | SIMPLE      | c     | ref   | post_id          | post_id | 8       
| cu9eeb_dev0806.a.ID       | 16     | Using where; Using index; Not 
exists |
+------+-------------+-------+-------+------------------+---------+---------+---------------------------+--------+--------------------------------------+
3 rows in set (0.001 sec)

MariaDB [cu9eeb_dev0806]> CREATE INDEX post_id_meta_key ON wp_postmeta 
(post_id, meta_key(191));
Query OK, 0 rows affected (4.202 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [cu9eeb_dev0806]> show index from wp_postmeta;
+-------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table       | Non_unique | Key_name         | Seq_in_index | Column_name 
| Collation | Cardinality | Sub_part | Packed | Null | Index_type | 
Comment | Index_comment | Ignored |
+-------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| wp_postmeta |          0 | PRIMARY          |            1 | post_id     
| A         |      537197 |     NULL | NULL   |      | BTREE      |         
|               | NO      |
| wp_postmeta |          0 | PRIMARY          |            2 | meta_key    
| A         |     1611591 |     NULL | NULL   |      | BTREE      |         
|               | NO      |
| wp_postmeta |          0 | PRIMARY          |            3 | meta_id     
| A         |     1611591 |     NULL | NULL   |      | BTREE      |         
|               | NO      |
| wp_postmeta |          0 | meta_id          |            1 | meta_id     
| A         |     1611591 |     NULL | NULL   |      | BTREE      |         
|               | NO      |
| wp_postmeta |          1 | meta_key         |            1 | meta_key    
| A         |      123968 |     NULL | NULL   |      | BTREE      |         
|               | NO      |
| wp_postmeta |          1 | meta_key         |            2 | meta_value  
| A         |     1611591 |       32 | NULL   | YES  | BTREE      |         
|               | NO      |
| wp_postmeta |          1 | meta_key         |            3 | post_id     
| A         |     1611591 |     NULL | NULL   |      | BTREE      |         
|               | NO      |
| wp_postmeta |          1 | meta_key         |            4 | meta_id     
| A         |     1611591 |     NULL | NULL   |      | BTREE      |         
|               | NO      |
| wp_postmeta |          1 | meta_value       |            1 | meta_value  
| A         |     1611591 |       32 | NULL   | YES  | BTREE      |         
|               | NO      |
| wp_postmeta |          1 | meta_value       |            2 | meta_id     
| A         |     1611591 |     NULL | NULL   |      | BTREE      |         
|               | NO      |
| wp_postmeta |          1 | post_id_meta_key |            1 | post_id     
| A         |      537197 |     NULL | NULL   |      | BTREE      |         
|               | NO      |
| wp_postmeta |          1 | post_id_meta_key |            2 | meta_key    
| A         |     1611591 |      191 | NULL   |      | BTREE      |         
|               | NO      |
+-------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
12 rows in set (0.001 sec)

MariaDB [cu9eeb_dev0806]> explain SELECT b.post_id, b.meta_value FROM 
`wp_posts` a LEFT JOIN `wp_postmeta` b ON b.post_id = a.ID AND b.meta_key 
= '_wp_attachment_metadata' LEFT JOIN `wp_litespeed_img_optm` c ON  
c.post_id = a.ID WHERE a.post_type = 'attachment' AND a.post_status = 
'inherit' AND a.post_mime_type IN ('image/jpeg', 'image/png', 'image/gif') 
AND c.id IS NULL ORDER BY a.ID DESC;
+------+-------------+-------+-------+-----------------------------------+---------+---------+---------------------------+--------+--------------------------------------+
| id   | select_type | table | type  | possible_keys                     | 
key     | key_len | ref                       | rows   | Extra                                
|
+------+-------------+-------+-------+-----------------------------------+---------+---------+---------------------------+--------+--------------------------------------+
|    1 | SIMPLE      | a     | index | type_status_date                  | 
PRIMARY | 8       | NULL                      | 343743 | Using where                          
|
|    1 | SIMPLE      | b     | ref   | PRIMARY,meta_key,post_id_meta_key | 
PRIMARY | 1030    | cu9eeb_dev0806.a.ID,const | 1      | Using where                          
|
|    1 | SIMPLE      | c     | ref   | post_id                           | 
post_id | 8       | cu9eeb_dev0806.a.ID       | 16     | Using where; 
Using index; Not exists |
+------+-------------+-------+-------+-----------------------------------+---------+---------+---------------------------+--------+--------------------------------------+
3 rows in set (0.001 sec)

[rjasdf]

Detailed analysis

Another way to measure is as follows, using the commandline tool "mysql":

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

Post the two sets (with and without INDEX) of results here; I will interpret the results.

Caveat: On some cloud services, you may not be allowed to do the FLUSH. In this case, the math is messier, but possible:

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

The technique gets the exact number of rows hit. If it shows a value of 2.4M or some multiple of that, the query did one (or more) full table scans. Or a full index scan. Or a Handler value mibht the same as the number of output rows.

Redundant index

The table seems to have both of these:

INDEX(post_id, meta_key(191))   -- toss
INDEX(post_id, meta_key, meta_id)   -- keep

DROP the first, keep the second.

Missing indexes?

Does wp_litespeed_img_optm have

INDEX(post_id)   (or an index starting with `post_id`?)

Does wp_post have

INDEX(post_type, post_status, post_mime_type, ID)

[OllieJones]

I am closing this issue. The index on wp_postmeta recommended by the web host is clearly and unambigously:

1. Redundant with the primary key added by this plugin. It just wastes tablespace.
2. Inferior to that index because it only handles the (191) prefix of meta_key.

I suggest you remove the index suggested by the web host.