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` ,
FROM `tbl-1`
WHERE `g` IS NULL AND (`c` = '12345')
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

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

Sorry, you can't reply to this topic. It has been closed.

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.