CODEWITHSUNDEEP
javamysqlperformancedatabase-performancequery-performance
kasdega
kasdega

Reputation: 18786

Mysql and Java search matching multiple values in multiple columns

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 1=1
   AND (p.idNumber = '#{term}'
          OR LOWER(p.firstName) LIKE '%#{term}%'
          OR LOWER(p.lastName) LIKE '%#{term}%' 
          OR LOWER(p.companyName) LIKE '%#{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?

Upvotes: 1

Views: 1683

Answers (1)

maaartinus
maaartinus

Reputation: 46472

You search can't use any index, that's why it's slow. What you're doing is a fulltext search, so try to use the existing feature.

Some details:

Why no index can be used?

  1. You're converting the column value to lowercase
  2. You're searching with '%' + ...

You're needlessly converting term to lowercase in every line again and again.

And you've probably never heard of SQL injection.

Would dynamic SQL help?

Probably yes, as fewer data would have to be transfered, but the more complicated query could be a problem for the optimizer. Mysql is not believed to be the smartest. Anyway, fulltext search is much faster as it uses an index.

What if fulltext search doesn't do exactly what I need?

You can try use it for getting a superset of what you want and filter further.

You could try to mimic it by creating a kitchen sink table containing all searchable text which would be updated by triggers, but this is quite some work and you won't get as fast as the engine itself.

Upvotes: 2

Related Questions