MySQL Forums
Forum List  »  InnoDB

Full Table Scans When LIMIT / OFFSET Are Used
Posted by: Carlos Mennens
Date: September 22, 2021 08:50AM

I am having CPU spikes during production caused by a specific query which is doing full table scans to produce the data. I used EXAMINE to better understand the query below:

SELECT `a` ,
CAST(`type` AS signed) AS `b` ,
`c` ,
`d`
FROM `tbl-1`
WHERE `g` IS NULL AND (`c` = '12345')
ORDER BY `a`
LIMIT 18446744073709551610 OFFSET 0;

When I explain the above query, the key used is totally different and Extra column = Using where.

When I remove the LIMIT / OFFSET keywords from the query, the engine uses a totally different key (index) & the Extra column now shows = Using index condition; Using filesort.

I checked and the application is not sending the SQL query with the LIMIT / OFFSET keywords in the statement so those might be implied somewhere / somehow.

Can anyone tell me if I'm in the right ball park here for trying to understand why this query above is using a totally different index than we expect it to be and when we omit the LIMIT / OFFSET values, the EXPLAIN plan looks optimized.

Appreciate any info!

Options: ReplyQuote


Subject
Views
Written By
Posted
Full Table Scans When LIMIT / OFFSET Are Used
106
September 22, 2021 08:50AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.