2

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.

7
  • @MaxVernon that is exactly correct
    – kasdega
    Oct 3, 2014 at 20:45
  • 1
    I recommend you to drop the column like '%term%' syntax and start using FULLTEXT indexes: dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html
    – jynus
    Oct 5, 2014 at 14:58
  • Also, the lower() syntax is in most cases useless, as the default collation tends to be case insensitive.
    – jynus
    Oct 5, 2014 at 15:00
  • I removed the LOWER syntax already, thanks for that suggestion...From what I have seen, the FULLTEXT indexing doesn't allow for both prefix and suffix wild card searching...*term* doesn't work only term* works.
    – kasdega
    Oct 6, 2014 at 15:46
  • In most cases, wildcards are not needed unless you need to search for substrings, as the index only matches full words.
    – jynus
    Oct 6, 2014 at 16:18

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.