optimizing a query containing regexp for sorting

Question

I need to optimise this query

SELECT DISTINCT abc.*
   FROM abc, xyz, xyz_value 
   WHERE abc.CategoryID IN ( $category_children ) 
   $where_var AND abc.Removed = 0 AND 
         xyz_value.Removed = 0 AND abc.abcID = xyz_value.GenericID AND 
         xyz_value.AttributeID = xyz.AttributeID $narrow_query
   ORDER BY 
         if(abc.Title REGEXP ('^[0-9]') = 1,cast(abc.Title as UNSIGNED),999999) ASC,
         if(abc.Title REGEXP ('^[0-9]') = 1,'z',abc.Title) $order_how
   $limit

$category_children is an array containing a bunch of category IDs. The attribute to be sorted contains string starting from digits, alphabets and also special characters. This query is getting slow due to regular expression used for sorting having execution time of 3 secs. And it takes .01 secs without sorting. How can I reduce its execution time? Any type of help is greatly appreciated.

You’d probably be better off if you kept that value in a dedicated sort column, that you update with the correct sort value whenever the `Title` column value is changed. That way, the engine can make use of an index on that sort column. — CBroe, Feb 09 '15 at 12:04

score 0 · Answer 1 · edited May 23 '17 at 12:27

I have created a demo and tested it without using Regular expression:

select IF(concat('',SUBSTRING(title,1,1)*1) = SUBSTRING(title,1,1),'true','false' ) as col from test;

Basic idea is taken from Detect if value is number in MySQL. I have found first string of title and checked if it is number or not.

DEMO In this Demo i have written both query having Regex and not having regex. What i have found without using REGEX query taking less time. Try to un-comment one query at a time and see execution time.

I haven't tested it with large amount of data. @Keshav sir can test this.

optimizing a query containing regexp for sorting

1 Answers1