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.
column like '%term%'
syntax and start usingFULLTEXT
indexes: dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.htmllower()
syntax is in most cases useless, as the default collation tends to be case insensitive.