Announcement

Announcement Module
Collapse
No announcement yet.

WHERE name LIKE '%sssss%'

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #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

    Comment


    • #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.

      Comment

      Working...
      X