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.