2

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      |         |               |
+----------+------------+---------------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4
  • 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, 2020 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. Oct 19, 2020 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, 2020 at 13:34
  • 1
    I would guess that it might have something to do with all of those highly inefficient LIKES.
    – vancoder
    Oct 19, 2020 at 15:21

1 Answer 1

1

Looking at your query, I see lots of stuff like wp_posts.post_content LIKE '%اهمية%'.

You should know that column LIKE '%matchstring%' is a notorious MySQL performance-killer. If you can change your operations to use column LIKE 'matchstring%' (with no leading %) this will get better.

Or, maybe it makes sense for you to adopt a search plugin like Relevanssi. (I don't know if it works well for Arabic, but it does work well for Roman-alphabet languages.)

1
  • I recall wondering about similar some time ago for an rtl language search, i.e. where the leading % is for rtl and how MySQL handles rtl :-)
    – birgire
    Aug 6, 2021 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.