CODEWITHSUNDEEP
phpmysql
user3304007
user3304007

Reputation: 470

mySQL - Regexp or LIKE in multiple search?

This script searches multiple keywords.

SELECT ID,SURNAME FROM people where name='john' 
and SURNAME REGEXP 'search1|search2|search3' 
ORDER BY id DESC

However, I have heard that regexp has lower performance and never use indexes. Is there a better/faster way to do the same ?

Upvotes: 0

Views: 31

Answers (1)

Bobert123
Bobert123

Reputation: 113

it depends a lot on the size of your dataset and the surname column. it also depends on how many surnames you end up including in your search.

I suggest testing it with a few different search terms and seeing if this is faster:

SELECT ID,SURNAME FROM people where name='john' 
and (SURNAME LIKE '%search1%' OR SURNAME LIKE '%search2%' OR SURNAME LIKE '%search3%') 
ORDER BY id DESC

note that if your search only needs a wildcard at the end, you can use 'search1%' instead of '%search1%' which should be faster

Upvotes: 1

Related Questions