I need to speed up a search.
Currently we let our users input a name: John Doe
Then in Java we split that string to create multiple values (or "terms") ["John", "Doe"] and loop through the terms provided doing a select in the database looking for that term in a number of relevant columns:
// For each term in the string[]
SELECT *
FROM Person p
WHERE (p.idNumber = #{term}
OR LOWER(p.firstName) LIKE LOWER(CONCAT('%', #{term}, '%'))
OR LOWER(p.lastName) LIKE LOWER(CONCAT('%', #{term}, '%'))
OR LOWER(p.companyName) LIKE LOWER(CONCAT('%', #{term}, '%'))
OR p.phone = #{term});
at the end of the loop we use Java to do an intersection of the matched rows so that all matches are unique.
persons = (List<Person>)CollectionUtils.intersection(persons, tmpPersons);
I'm planning on changing this to a dynamic SQL statement to match all terms provided in one sql statement and removing the CollectionUtils.intersection but before I go through that process is there another way to do this that produce a faster search?
We're using Mysql 5.5 and an InnoDB table.
Best Answer
You're correct:
FULLTEXT
search didn't hit InnoDB until MySQL 5.6. This leaves you with a few options:FULLTEXT
indexSince you're building the query in Java, I'd also suggest removing the
CONCAT('%'
stuff in favor of just creating a second variable that has the%
s already applied. That saves the DB from the extra work of parsing it and lets it utilize indexes better. Speaking of indexes, be sure you have indexes that span the columns where possible.Regarding the Duplicate removal, you may be better off performing a
GROUP BY p.idNumber
to save the extra effort and data transfer in Java.