Different performance using limit less than 100 and greater than 100

Question

I have a subselect that filters 16 records and there is a limit of 100.

This consultation results in a time usually of 1:20 ~ 1:40 minutes. But when I change the limit to a number greater than 100, be it 200, 300 or 1k the result is returned in less than 1 second. The time is lower also when using a limit less than or equal to the amount of records available.

I performed a EXPLAIN of this select and there really is a change in the way the system performs the search.

I wonder if there is how to optimize this search while keeping a low limit. Mysql has some limit-related configuration to cause this big difference?

select consultas.id       
  from consultas                          
 where find_in_set(status,'Ok')
   and tipo_doc like '%qualquer%'
   and id_empresas in (6226,6416)
   and cnpj != '09966999000100'
   and case utilizado 
       when "S" then "Importado"  
       when "N" then "Pendente"
        end like '%Pendente%'  
 order by consultas.id desc                         
 limit 1000(ou 100)

Explain example with limit = 100

Explain example with limit = 1k

Answer 1

I didn’t understand why use the case in this context, find_in_set(status,'Ok') instead of status like '%Ok%', changing the query as below improves performance?

select consultas.id       
  from consultas                          
 where status like '%Ok%'
   and tipo_doc like '%qualquer%'
   and id_empresas in (6226,6416)
   and cnpj != '09966999000100'
   and utilizado = 'N'
 order by consultas.id desc                         
 limit 1000(ou 100)