1

I have table that contains five million addresses as strings. There is a user interface that allows you to search by address and it is implemented in the simplest possible way - using the LIKE syntax with wildcards at the both ends of the searched string. However this approach is really slow (especially when search result has to be sorted by some criteria). I thought of using fulltext search as it would be much faster but it limits users and I dont want it since most search queries contain just part of word. Addresses also can be abbreviated.

What can I do? How could I implement the address search to be more efficient but without sacrificing much of the user experience.

Edit Ive just read that I can actually use a wildcard at the end of the string when performing match against fulltext search. So if i store the reversed address maybe I can do prefix wildcard fulltext search. But will it be faster and how much? Are there some benchmarks? I couldn find one.

alexanderg
  • 177
  • 1
  • 9

1 Answers1

0

You may want to look into Full-Text Searches. The LIKE operator in SQL is very inefficient, especially when you have that many records.

Javadocs
  • 120
  • 1
  • 7
  • 1
    The OP specifically mentioned Fulltext in his question, and why he fears it may not be applicable to his needs. `LIKE` can be extremely efficient, but only where the pattern can be matched against an index prefix. – eggyal Mar 06 '13 at 23:14
  • That he did; I missed that part. However, I'm not sure how the full-text search limits the user in his case. – Javadocs Mar 06 '13 at 23:21
  • 1
    Nor me. But it certainly won't help when "*most search queries contain just part of word*". – eggyal Mar 06 '13 at 23:23
  • @eggyal You can construct your full-text search to search parts of the words using `"` and `*`. [This question](http://stackoverflow.com/questions/2962776/using-full-text-search-in-order-to-find-partial-words-sql-server-2008) has a good example, although its MSSQL, not mySQL. – Javadocs Mar 06 '13 at 23:36