Mysql search matching multiple values in multiple columns

Question

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.

Answer 1

You're correct: FULLTEXT search didn't hit InnoDB until MySQL 5.6. This leaves you with a few options:

1. Update to MySQL 5.6 and use a FULLTEXT index
2. Change the contract of your function to only allow prefix searches; that is, 'term%'. It will fulfill many use cases while saving your DB.
3. Convert to a MyISAM table, or create a spare MyISAM table that you can join on specifically for searches.

Since 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.