Scaling Mysql column oriented - Wordpress

Question

For those DBAs not familiar with WP, it has a Column oriented structure (like many open source designed to serve most use cases) that is killing performance exponentially as rows increase.

I know that many OpenSource CMSs was designed to cover most use cases not to performs. That the reason they use column oriented db design.

We replaced many queries by ElasticSearch, but Mysql is still our primary datastore and I guess some SQL queries are still needed for data consistency.

If we move to our own Mysql table structure, we will lost all the benefit of a rich and widely used WP API. (mainly WP_query() )

Is something obvious we are not doing correct like simple query optimization or relative simple DBA tasks that can help us reach, not billions but 20MM rows.

Here are some examples of slow queries.

EXPLAIN SELECT COUNT(*) FROM wp_posts JOIN (SELECT `post_id` FROM wp_postmeta WHERE `meta_key` = 'et_payment_package' AND `meta_value` IN (246)) as metas ON `ID` = `post_id` WHERE `post_author` = 25492 AND `post_status` = 'publish';



+----+-------------+-------------+--------+---------------------+----------+---------+---------------+--------+-------------+
| id | select_type | table       | type   | possible_keys       | key      | key_len | ref           | rows   | Extra       |
+----+-------------+-------------+--------+---------------------+----------+---------+---------------+--------+-------------+
|  1 | PRIMARY     | <derived2>  | ALL    | NULL                | NULL     | NULL    | NULL          | 110310 |             |
|  1 | PRIMARY     | wp_posts    | eq_ref | PRIMARY,post_author | PRIMARY  | 8       | metas.post_id |      1 | Using where |
|  2 | DERIVED     | wp_postmeta | ref    | meta_key,meta_value | meta_key | 203     |               | 217442 | Using where |
+----+-------------+-------------+--------+---------------------+----------+---------+---------------+--------+-------------+
3 rows in set (5.27 sec)

Another query:

Query_time: 23.991255  Lock_time: 0.000174 Rows_sent: 6  Rows_examined: 402008

SELECT wp_users.ID,wp_users.user_login,wp_users.display_name FROM wp_users INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id ) WHERE 1=1 AND ( ( wp_usermeta.meta_key = 'wp_user_level' AND CAST(wp_usermeta.meta_value AS CHAR) != '0' )) ORDER BY display_name ASC;

+----+-------------+-------------+------+------------------+----------+---------+-----------------------------+--------+----------------+
| id | select_type | table       | type | possible_keys    | key      | key_len | ref                         | rows   | Extra          |
+----+-------------+-------------+------+------------------+----------+---------+-----------------------------+--------+----------------+
|  1 | SIMPLE      | wp_users    | ALL  | PRIMARY          | NULL     | NULL    | NULL                        | 127267 | Using filesort |
|  1 | SIMPLE      | wp_usermeta | ref  | user_id,meta_key | meta_key | 111     | const,tdm_wp_cl.wp_users.ID |      1 | Using where    |
+----+-------------+-------------+------+------------------+----------+---------+-----------------------------+--------+----------------+

This code provided by @Strawberry run very fast. Anyone know if this can be done using standard WP_query API?

EXPLAIN SELECT COUNT(*) FROM wp_posts p JOIN wp_postmeta m ON p.ID = m.post_id WHERE p.post_author = 25492 AND p.post_status = 'publish'  And m.meta_key = 'et_payment_package' AND m.meta_value IN (246);
+----+-------------+-------+------+-----------------------------+-------------+---------+----------------+------+-------------+
| id | select_type | table | type | possible_keys               | key         | key_len | ref            | rows | Extra       |
+----+-------------+-------+------+-----------------------------+-------------+---------+----------------+------+-------------+
|  1 | SIMPLE      | p     | ref  | PRIMARY,post_author         | post_author | 8       | const          |   12 | Using where |
|  1 | SIMPLE      | m     | ref  | meta_key,post_id,meta_value | post_id     | 8       | tdm_wp_cl.p.ID |    3 | Using where |
+----+-------------+-------+------+-----------------------------+-------------+---------+----------------+------+-------------+
2 rows in set (0.01 sec)

Strawberry · Accepted Answer · 2015-12-23 18:03:25Z

1

There are optimisations. For instance, you have an unnecessary subquery in your first query:

SELECT COUNT(*) 
   FROM wp_posts p
    JOIN wp_postmeta m
    ON p.ID = m.post_id
 WHERE p.post_author = 25492
  AND p.post_status = 'publish' 
 And m.meta_key = 'et_payment_package' 
  AND m.meta_value IN (246)

Similarly, I suspect that the CAST function prohibits the use of an index

SELECT u.ID
, u.user_login
, u.display_name 
FROM wp_users u
JOIN wp_usermeta m
 ON u.ID = m.user_id
WHERE m.meta_key = 'wp_user_level' 
AND m.meta_value != 0
ORDER 
 BY display_name ASC;

edited Dec 23, 2015 at 18:03

answered Dec 23, 2015 at 17:56

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Francisco Campaña Over a year ago

Thanks, your query is incredibly faster. Do you, or someone else know if it can be done using WP_query API.

Francisco Campaña Over a year ago

Sorry I'm newbie, how can I approve the answer?

Collectives™ on Stack Overflow

Scaling Mysql column oriented - Wordpress

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related