Bug #83719 REGEXP is a lot slower than LIKE
Submitted: 7 Nov 2016 10:26 Modified: 7 Dec 2016 11:02
Reporter: james wang Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[7 Nov 2016 10:26] james wang 
Description:
Hi,
Here is an example: 
select * from tablea a where a.acolumn regexp '^pat1|^pat2|^pat3';

is a lot slower than:

select * from tablea a where a.acolumn like 'pat1%' or a.acolumn like 'pat2%' or a.acolumn like 'pat3%';

a.acolumn is indexed.

Please improve this as I do enjoy the query simplicity REGEXP brings in.

Thanks

How to repeat:
Here is an example: 
select * from tablea a where a.acolumn regexp '^pat1|^pat2|^pat3';

is a lot slower than:

select * from tablea a where a.acolumn like 'pat1%' or a.acolumn like 'pat2%' or a.acolumn like 'pat3%';

a.acolumn is indexed.

Suggested fix:
Translate REGEXP to LIKE (for simple queries) internally
[7 Nov 2016 11:02] MySQL Verification Team 
Thank you for the bug report. Please provide the complete test case (create table, insert data, etc) not just a partial description and the server version and OS. Thanks.
[8 Dec 2016 1:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".