Full Table Scans When LIMIT / OFFSET Are Used
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!
Subject
Views
Written By
Posted
Full Table Scans When LIMIT / OFFSET Are Used
1475
September 22, 2021 08:50AM
538
September 22, 2021 10:38AM
499
September 22, 2021 03:16PM
462
September 22, 2021 03:27PM
624
September 22, 2021 05:34PM
453
September 22, 2021 08:09PM
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.