Not the answer you need?
Register and ask your own question!

WHERE name LIKE '%sssss%'

sjr115sjr115 EntrantInactive User Role Participant
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

Comments

  • jkraaijkraai Entrant Inactive User Role Participant
    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
  • MarkRoseMarkRose Contributor Inactive User Role Advisor
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.