mySQL - Regexp or LIKE in multiple search?

Question

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 ?

That depends, are you looking for a partial match or an exact match ? E.G. SURNAME = 'search1' or SURNAME LIKE '%search1%' ? — sagi
– sagi, Commented Sep 11, 2016 at 9:50

Bobert123 · Accepted Answer · 2016-09-11 11:02:16Z

1

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

answered Sep 11, 2016 at 11:02

Bobert123

1139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mySQL - Regexp or LIKE in multiple search?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related