1

Hi I need help with below query this query takes around 20 second and when more then one is executed then server CPU usage hits 100% and server stop responding.

here is the query from process list.

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND (((wp_posts.post_title LIKE '%اهمية%') OR (wp_posts.post_excerpt LIKE '%اهمية%') OR (wp_posts.post_content LIKE '%اهمية%')) AND ((wp_posts.post_title LIKE '%التصنيع%') OR (wp_posts.post_excerpt LIKE '%التصنيع%') OR (wp_posts.post_content LIKE '%التصنيع%')) AND ((wp_posts.post_title LIKE '%الغذائي%') OR (wp_posts.post_excerpt LIKE '%الغذائي%') OR (wp_posts.post_content LIKE '%الغذائي%'))) AND (wp_posts.post_password = '') AND wp_posts.post_type IN ('post', 'attachment') AND (wp_posts.post_status = 'publish') ORDER BY (CASE WHEN wp_posts.post_title LIKE '%اهمية التصنيع الغذائي %' THEN 1 WHEN wp_posts.post_title LIKE '%اهمية%' AND wp_posts.post_title LIKE '%التصنيع%' AND wp_posts.post_title LIKE '%الغذائي%' THEN 2 WHEN wp_posts.post_title LIKE '%اهمية%' OR wp_posts.post_title LIKE '%التصنيع%' OR wp_posts.post_title LIKE '%الغذائي%' THEN 3 WHEN wp_posts.post_excerpt LIKE '%اهمية التصنيع الغذائي %' THEN 4 WHEN wp_posts.post_content LIKE '%اهمية التصنيع الغذائي %' THEN 5 ELSE 6 END), wp_posts.post_date DESC LIMIT 0, 10

here is the index on wp_posts table

+----------+------------+---------------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_name                             |            1 | post_name     | A         |      172779 |      191 | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date                      |            1 | post_type     | A         |          32 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date                      |            2 | post_status   | A         |          42 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date                      |            3 | post_date     | A         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date                      |            4 | ID            | A         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_parent                           |            1 | post_parent   | A         |       86389 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_author                           |            1 | post_author   | A         |         226 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | posttype_new                          |            1 | post_type     | A         |          32 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | posttype_new                          |            2 | ID            | A         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | posttype_new                          |            3 | post_status   | A         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_Type_status_date                   |            1 | ID            | A         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_Type_status_date                   |            2 | post_type     | A         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_Type_status_date                   |            3 | post_status   | A         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wpi_scalability_pro_sitemaps          |            1 | post_status   | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wpi_scalability_pro_sitemaps          |            2 | post_password | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wpi_scalability_pro_sitemaps          |            3 | post_type     | A         |          40 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wpi_scalability_pro_sitemaps          |            4 | post_modified | A         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wpi_scalability_pro_sitemaps_postdate |            1 | post_status   | A         |          14 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wpi_scalability_pro_sitemaps_postdate |            2 | post_password | A         |          14 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wpi_scalability_pro_sitemaps_postdate |            3 | post_type     | A         |          42 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wpi_scalability_pro_sitemaps_postdate |            4 | post_date     | A         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+---------------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  • 2
    Can you discover the query from the WP end? A tool such as Query Monitor should expose this, otherwise I cannot distinguish anything particularly expensive about that query, are you sure that's the query that's problematic and not just what was ran just before the query that was problematic? How did you identify the index as a problem? – Tom J Nowell Oct 19 at 12:41
  • Hi i found this query by show processlist in MySQL and slow log, then i executed this one manually and it took around 20 seconds. I am using query monitor on WordPress but unable to found this one. one more thing that I noticed in the show processlist is like there is some sort of ajax request in WordPress because for each character searched i am seeing a query in processlist and i can't figure it out on WordPress from where it is coming from. – Danial Akhter Oct 19 at 13:10
  • Query Monitor is supposed to list those queries and the code that made them. Without knowing what triggered the query there is very little that can be done to help. We need to know where it is triggered from. Likely it is one of several triggered by a WP_Query object, and since you know it's triggered when you type, that means you can follow the network request to identify the AJAX call, and thus the code handling it – Tom J Nowell Oct 19 at 13:34
  • I would guess that it might have something to do with all of those highly inefficient LIKES. – vancoder Oct 19 at 15:21

Your Answer

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

Browse other questions tagged or ask your own question.