1

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?

Improve this question
1
  • for your consideration: use a mix of JPA/JavaFXLambdas; Commented Oct 3, 2014 at 21:06

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you for your response. I have heard of SQL injection and have already taken measures to stop it in this scenario, I do appreciate your concern though. My understanding is that Mysql introduced FULLTEXT for INNODB in version 5.6. I'm harnessed with Mysql 5.1 and an INNODB table so my only option as I understand it is to dynamically build a big sql statement for each "term".
@kasdega One problem is that no search starting with % can use indexed. Ending with % is fine. Instead of converting to lowercase you could probably make the column case insensitive. So if you could restrict yourself to searching for column prefixes, then you're lucky. Otherwise, you'd need quite some triggers to make your own fulltext if you want a decent speed.
I removed the LOWER and the CONCAT parts of the search and it did get a little faster. Still isn't as fast as FULLTEXT could be, one other major problem with FULLTEXT in MYISAM is that MYISAM uses table level locking which would be a complete disaster for our application. It doesn't appear that I have many options but thank you for the info you have provided it has helped.

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.