Slow query (SELECT posts.ID)

[Bt6K]

Hello,

We're running WordPress 6.1.1, with the latest version of index-wp-mysql-for-speed.

We enabled the MySQL slow query log and we repeatedly see this:

# Time: 230331 13:56:54
# User@Host: mydirty1_wp438[mydirty1_wp438] @ localhost []
# Thread_id: 2674  Schema: mydirty1_v3grh4hW3tzh4zh  QC_hit: No
# Query_time: 2.420404  Lock_time: 0.000198  Rows_sent: 12  Rows_examined: 220837
# Rows_affected: 0  Bytes_sent: 219
SET timestamp=1680267414;
SELECT   posts.ID
                                        FROM posts
                                        WHERE 1=1  AND posts.ID NOT IN (215390) AND (
  posts.ID IN (
                                SELECT object_id
                                FROM term_relationships
                                WHERE term_taxonomy_id IN (6)
                        )
) AND posts.post_type = 'post' AND ((posts.post_status = 'publish'))
                                        GROUP BY posts.ID
                                        ORDER BY RAND()
                                        LIMIT 0, 12;

And:

# Time: 230331 13:57:05
# User@Host: mydirty1_wp438[mydirty1_wp438] @ localhost []
# Thread_id: 2702  Schema: mydirty1_v3grh4hW3tzh4zh  QC_hit: No
# Query_time: 2.169809  Lock_time: 0.000246  Rows_sent: 15  Rows_examined: 73491
# Rows_affected: 0  Bytes_sent: 252
SET timestamp=1680267425;
SELECT SQL_CALC_FOUND_ROWS  posts.ID
                                        FROM posts
                                        WHERE 1=1  AND (((posts.post_title LIKE '%2023%') OR (posts.post_excerpt LIKE '%2023%') OR (posts.post_content LIKE '%2023%')))  AND (posts.post_password = '')  AND posts.post_type IN ('post', 'page', 'attachment') AND ((posts.post_status = 'publish'))

                                        ORDER BY posts.post_title LIKE '%2023%' DESC, posts.post_date DESC
                                        LIMIT 0, 42;

I was wondering if there is anything that can be added to the plugin to help deal with these cases?

Many thanks

[lkraav]

I think both ORDER BY RAND() and LIKE '%2023% with double-sided wildcard are unassistable performance killers.

[rjasdf]

The plugin only affects INDEXes. The problems with those queries are with the formulation of the queries. WP, itself, controls that. However, the user may be able to avoid some of the problems.

• IN ( SELECT ... ) would be better off as EXISTS ( SELECT ... ) or JOIN ....
• Leading wildcards in LIKE prevents the use of an index. "2023" looks like a year. Perhaps this is a generic data entry field that you then search in 3 fields for? Can you engage the user's help by having multiple, less-generic, forms that lead to different query formulations?
• OR and LIKE with a leading wildcard could be turned into a FULLTEXT search without OR. But this would require major changes to the core WP code.
• ORDER BY RAND() LIMIT ... cannot be optimized without major changes.
• posts.ID NOT IN (215390) together with LIMIT 12 is hard to optimize; Removing the ID test may improve things, but at the expense of sometimes returning a row you don't want. Or getting 13 rows, then removing that one (or some other one). But that is quite messy.

Rows_sent: 12 Rows_examined: 220837 -- This indicates that the INDEXes are inadequate; I am saying that no other INDEX would help.

Please provide EXPLAIN SELECT ... so we can verify whether the Optimizer is using the indexes that it could use.

The bottom line is that WP is designed for lightweight usage, not million-row datasets. Perhaps custom SQL and non-trivial schema changes could lead to a performance fix for these and similar queries.

[OllieJones]

Rick @rjasdf is correct. If I were you I would take up this problem with the author of the plugin that generates this query.

Thanks for bringing this to our attention.