WHERE name LIKE '%sssss%'

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

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

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.