WHERE name LIKE '%sssss%'

MySQL & MariaDB Other MySQL® Questions
1

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

2

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

3

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.