CODEWITHSUNDEEP
phpmysql
Rahul R
Rahul R

Reputation: 207

Any Other Faster Execution Method For This MYSQL search query?

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.

Upvotes: 0

Views: 107

Answers (3)

Bart Friederichs
Bart Friederichs

Reputation: 33573

  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

Upvotes: 0

Barmar
Barmar

Reputation: 782693

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.

Upvotes: 1

Dave
Dave

Reputation: 3288

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

Upvotes: 0

Related Questions