Query only for the first n results of like

Question

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

1. how to, if possible, query only for the first n results of my query
2. If there is any possible INDEX I could define to make querys like this faster for the given circumstances.

Answer 1

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 + "%"

Answer 2

Full text searches are often quite slow, specifically if you have a leading % matcher. Also, note that jOOQ's, equalsIgnoreCase() really also adds a LOWER() function call to your column, which prevents index usage. You might not want to do that. To see what SQL code is rendered by jOOQ, enable jOOQ's debug logging. This will also help you assess whether the query is slow, or the fetching.

Given your questions:

1. Use jOOQ's limit(..).offset(..) clause.
2. Yes, MySQL supports a fulltext index. If this query is run a lot, you'll get better results with it.