WHERE name LIKE '%sssss%'

[sjr115]

I have an InnoDB table that has 100k records and I need to do a search on this table on one column for a “quick search” style search box. The where clauses is currently using ’ …WHERE name LIKE ‘%xyz%’ LIMIT 20’, but this is proving too slow.

Any suggestions?

Sam

[jkraai]

Sam,

sphinx

maybe faster is to not use a database

1. prep: on update of InnoDB table, add that column value to the end of a plain-text file
2. runtime: sys exec ‘grep -i “xyz” < tableColumn.txt | sort | uniq’
3. weekly maintenance:
   a. copy tableColumn.txt tableColumn.txt.bak
   b. sort < tableColumn.txt.bak | sort | uniq > tableColumn.txt
   c. unlink tableColumn.txt.bak

tableColumn.txt can be indexed
look at Agrep, don’t forget to browse the “See also” section.

ugh, i’m having late 90’s text processing flashbacks

–jim

[MarkRose]

MySQL can’t use an index for matching a column that begins with a wildcard.

I would look into Sphinx for that kind of search.