I am using MySQL.
I have several lookups like this:
SELECT * from users WHERE user_name LIKE '%search_term%'
What index do I need to make these lookups fast?
views:
73answers:
4
+2
A:
No index will help. It would be like looking in a dictionary for all words containing the substring "ab". You will still have to look at every word.
Have you looked into Full Text Search? http://dev.mysql.com/tech-resources/articles/full-text-revealed.html
Martin Smith
2010-04-02 15:52:17
Algorithms exists to build search trees like that (e.g. building a suffix tree for a string which consists of `user_name` s joined with a control character).Are you completely positive that mysql doesn't support it? It's just that I am not using MyISAM, but InnoDB
glebm
2010-04-02 16:11:02
Ah InnoDB doesn't support FTS. I think you'd have to roll your own solution or use HLGEM's or Jesse's suggestion. I doubt that there is anything inbuilt that will help you but am sure someone will correct me If I'm wrong!
Martin Smith
2010-04-02 16:39:42
+2
A:
If for some reason full-text search won't work for you (try it first it is the bestter solution), I had an issue like this where I searched first for the string LIKE 'search_term%' which can use the index and then only tried the longer search if the first one didn't return any records. The majority of people will search using the first letters of the search term and they can have faster returns. Tell them on the site that using the first letter of the search term will improve the performance of the search. OR even make it a requirement and avoid ever having to do the "search for these random letters I typed in" search.
HLGEM
2010-04-02 16:08:15
+2
A:
We used to run these kinds of queries too, but soon realized that relational databases like MySQL aren't ideal for full-text searching. Depending on what kind of other lookups you have, you might want to look at Lucene and Solr.
Lucene supports indexing and search, completely separate from your database. It's extremely fast and also supports spell checking, stemming, relevancy, and wildcard searches.
Solr is a search server powered by Lucene and also supports MySQL database integration. Highly recommended.
Jesse
2010-04-02 16:34:59
A:
Having a wildcard on both ends of the string, no index will make that faster. It will have to do a full table scan. However, if the wildcard string is only the end, e.g. 'term%', then an index will be used.
As others pointed out, fulltext index may work for you as well. However, it only supports "full word" searches, and (by default) only words with 4 or more characters.
Sean
2010-04-07 00:21:10