WordPress.org

Make WordPress Core

Opened 4 months ago

Last modified 4 months ago

#41278 new defect (bug)

SQL_CALC_FOUND_ROWS slow query

Reported by: lubimow Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 4.8
Component: Database Keywords:
Focuses: Cc:

Description

In the logs of slow queries when searching for steel, the following types of logs appear:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID 
FROM wp_posts  
WHERE 1=1  
AND (((wp_posts.post_title 
LIKE '%donald%') OR (wp_posts.post_excerpt 
LIKE '%donald%') OR (wp_posts.post_content 
LIKE ‘%donald%’)) AND ((wp_posts.post_title 
LIKE '%trump%') OR (wp_posts.post_excerpt 
LIKE ‘%trump%’) OR (wp_posts.post_content 
LIKE '%trump%')))  
AND wp_posts.post_type 
IN ('post', 'page', 'attachment') 
AND (wp_posts.post_status = 'publish' 
OR wp_posts.post_status = 'acf-disabled' 
OR wp_posts.post_author = 186 
AND wp_posts.post_status = 'private')  
ORDER BY (CASE WHEN wp_posts.post_title 
LIKE ‘%donald trump%' THEN 1 WHEN wp_posts.post_title 
LIKE ‘%donald%’ AND wp_posts.post_title 
LIKE '%trump%' THEN 2 WHEN wp_posts.post_title 
LIKE '%donald%' OR wp_posts.post_title 
LIKE '%trump%' THEN 3 WHEN wp_posts.post_excerpt 
LIKE '%donald trump%' THEN 4 WHEN wp_posts.post_content
LIKE '%donald trump%' THEN 5 ELSE 6 END), 
wp_posts.post_date DESC LIMIT 0, 30;

How can I eliminate it?

Change History (7)

#1 @SergeyBiryukov
4 months ago

  • Component changed from General to Database

#2 @SergeyBiryukov
4 months ago

  • Summary changed from SQL_CALC_FOUND_ROWS slow queri to SQL_CALC_FOUND_ROWS slow query

#3 follow-ups: @MaximeCulea
4 months ago

Is this related to the "no_found_rows" arg for \WP_Query for exemple ?

#4 in reply to: ↑ 3 @lubimow
4 months ago

Replying to MaximeCulea:

Is this related to the "no_found_rows" arg for \WP_Query for exemple ?

This happens when I search for links (when I want to link to a post) or search for a photo by media gallery

#5 in reply to: ↑ 3 @lubimow
4 months ago

Replying to MaximeCulea:

Is this related to the "no_found_rows" arg for \WP_Query for exemple ?

How do I test this?

#6 follow-up: @MaximeCulea
4 months ago

@lubimow,

SQL_CALC_FOUND_ROWS is used in most queries in order to implement pagination, even when you don’t need pagination at all. It tells to MySQL to do additional work for counting the total matching rows.

Inside \WP_Query and get_posts() it is done with the arg no_found_rows => false. So if you set it to true, it will improve your query speed.

#7 in reply to: ↑ 6 @lubimow
4 months ago

@MaximeCulea

Unfortunately my knowledge of wordpress is not very great, I will be very grateful if you tell me how to implement what you wrote

Note: See TracTickets for help on using tickets.