CODEWITHSUNDEEP
mysqlsql
supercoolville
supercoolville

Reputation: 9086

Most efficient way to search multiple things at once with SQL?

For example, I want to search all entries for "Sprite", "Pepsi", or "Coke" in the title/description and output the last 3 results. 

------------------------------------------------
| ID | Title                 | Description     |
------------------------------------------------
| 1  | text text SPRITE text | text PEPSI xxx  |
| 2  | text text text text   | text text text  |
| 3  | text text SPRITE text | text COKE xxxx  |
| 4  | text text text text   | text text text  |
| 5  | text PEPSI text text  | text COKE xxxx  |
| 6  | text COKE text text   | text COKE xxxx  |
| 7  | text text text text   | text text text  |
| 8  | text text text text   | text text text  |
------------------------------------------------

Is this the most efficient query to do this?

SELECT *
FROM `table`
WHERE title LIKE '%Sprite%' OR description LIKE '%Sprite%'
      OR title LIKE '%Pepsi%' OR description LIKE '%Pepsi%'
      OR title LIKE '%Coke%' OR description LIKE '%Coke%'
ORDER BY 'id' DESC
LIMIT 3;

Or is there a way to do it with MATCH AGAINST? (I couldnt find a way)..

Example output:

------------------------------------------------
| 6  | text COKE text text   | text COKE xxxx  |
| 5  | text PEPSI text text  | text COKE xxxx  |
| 3  | text text SPRITE text | text COKE xxxx  |
------------------------------------------------

Upvotes: 0

Views: 428

Answers (1)

kasavbere
kasavbere

Reputation: 6003

SELECT *
FROM `table`
WHERE description regexp ('sprite|pepsi|coke')
ORDER BY 'id' DESC

Upvotes: 1

Related Questions