0

I have 100K datas in my mysql database, I want to search a query in it. I removed stop-words and splitted it into an array of keywords and stored in a variable ie $key[0],$key[1],$key[2].I am using the following query 

SELECT * 
FROM   `table` 
WHERE (`column` LIKE '%$key1%' 
     OR `column` LIKE '%$key2%' 
     OR `column` LIKE '%$key3%');

is any other faster ways to do the same.

Aaron W.
  • 9,254
  • 2
  • 34
  • 45
Rahul R
  • 209
  • 2
  • 11
  • You can take the advantage for index if you can manage to remove widlcard(%) from beginning of search string. – metalfight - user868766 Jul 24 '13 at 12:58
  • You're using an inefficient way of storing keys - it's better to store keywords in one table, and have a link table containing the relationships. That lets you index things properly, and you don't have to worry about your keyword column running out of space because someone's used too many keywords. – andrewsi Jul 24 '13 at 13:01

3 Answers3

1

The only way to speed up queries like this is to use full-text searching. LIKE '%string%' can't be optimized with normal indexes, because they use B-trees that depend on matching the prefix of the string being searched for. Since your pattern begins with a wildcard, the index doesn't help.

Another solution is to normalize your database. Don't put the keywords all in one column, put them in another table, with a foreign key to this table and a row for each FK+keyword. Then you can use a join to match the keywords.

Also, you're using the wrong type of quotes around your column names. They should be backticks, not single quotes.

Barmar
  • 741,623
  • 53
  • 500
  • 612
0

you can do something like this

SELECT * 
FROM  table 
WHERE colomn REGEXP '$key1|$key2|$key3'

etc etc so instead of creating your array as a comma separated list of key words do it as a pipe separated list and then just push the string into your regex too this is simply an example

Dave
  • 3,280
  • 2
  • 22
  • 40
0
  1. Don't SELECT *, only select what you need.
  2. If you want to do complete-text searches, lose the % and add an index
  3. You misspelled column
Bart Friederichs
  • 33,050
  • 15
  • 95
  • 195
  • So if you have 255 columns and need 254, you still won't `SELECT *`? – N.B. Jul 24 '13 at 13:55
  • Really? Care to elaborate? – N.B. Jul 24 '13 at 19:25
  • There is a discussion here: http://stackoverflow.com/questions/3184478/how-many-columns-is-too-many-columns And while there might be cases where having a lot of columns can make sense, more often than not it is bad database design. Many-columned-tables often have a lot of redundancy or calculated values. – Bart Friederichs Jul 25 '13 at 06:53