CODEWITHSUNDEEP
mysqlregexselectmysql-slow-query-log
user3194762
user3194762

Reputation: 1

how to speed up mysql regex query

I want to develope a site for announcing jobs, but because I have a lot of conditions (title,category,tags,city..) I use a MySQL regex statement. However, it's very slow and sometimes results in a 500 internal Server Error

Here is one example :

select * from job 
where 
( LOWER(title) REGEXP 'dév|freelance|free lance| 3eme   grade|inform|design|site|java|vb.net|poo ' 
or 
LOWER(description) REGEXP 'dév|freelance|free lance| 3eme grade|inform|design|site|java|vb.net|poo '
 or
 LOWER(tags) REGEXP 'dév|freelance|free lance| 3eme grade|inform|design|site|java|vb.net|poo') 
and 
LOWER(ville) REGEXP LOWER('Agadir') 
and 
`date`<'2016-01-11' 
order by `date` desc

Any advice?

Upvotes: 0

Views: 1004

Answers (2)

Rick James
Rick James

Reputation: 142453

The big part of the WHERE, namely the OR of 3 REGEXPs cannot be optimized.

LOWER(ville) REGEXP LOWER('Agadir') can be turned into simply ville REGEXP 'Agadir' if your collation is ..._ci. Please provide SHOW CREATE TABLE job.

Then that can be optimized to ville = 'Agadir'.

But maybe this query is "generated" by your UI? And the users are allowed to use regexp thingies? (SECURITY WARNING: SQL injection is possible here!)

If it is "generated", the generate the "=" version if there are no regexp codes.

Provide these:

INDEX(ville, date) -- for cases when you can do `ville = '...'`
INDEX(date)        -- for cases when you must have `ville REGEXP '...'`

The first will be used (and reasonably optimal) when appropriate. The second is better than nothing. (It depends on how many rows have that date range.)

It smells like there may be other SELECTs. Let's see some other variants. What I have provided here may or may not help with them.

See my indexing cookbook: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Upvotes: 1

symcbean
symcbean

Reputation: 48387

You can't optimize a query based exclusively on regexes. Use full text indexing (or a dedicated search engine such as Mnogo) for text search and geospatial indexing for locations.

Upvotes: 1

Related Questions