Full Table Scans When LIMIT / OFFSET Are Used

[Carlos Mennens]

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!