Query only for the first n results of like What I'm trying to accomplish is a huge speedup (read: incremental results) when searching a database with a like operator which is kind of a last resort, if everything else already failed, to come up with a solution to the given search.

What I'm doing right now is that I query mysql with jooq like:

dsl.select(re.ID).from(re).where(re.EXPLANATION.likeIgnoreCase("%" + contains + "%")).fetch() 

which is equivalent to

SELECT ID from EXPLANATIONS where EXPLANATIONS.EXPLANATION like "%" + contains + "%"

but I really don't need the whole result (or well, not in the first step). The reason for the slow speed is that this database-table contains some 2 million records, and the EXPLANATION field is varchar(2000) which makes it kinda slow to query (there is however an FULLTEXT INDEX defined for this field).

My questions would be 


how to, if possible, query only for the first n results of my query
If there is any possible INDEX I could define to make querys like this faster for the given circumstances.
I understand you do not want only to limit the number of returned results (which could be done with a LIMIT at the end of the query), but the number of records that the LIKE operation is applied.

One idea could be, instead of apply the LIKE operation on all records of EXPLANATIONS table, doing on a limited number of items of the table:

SELECT exp.ID FROM(select * from EXPLANATIONS limit 1000) exp WHERE 
exp.EXPLANATION LIKE "%" + contains + "%"