Can you do something to optimize this kind of query?

mysql> explain  select * from wp_WP_SEO_Redirection where enabled=1 and cat='link' and blog='1' and regex<>'' and ('/tag/contestata/' regexp regex or '/tag/contestata'  regexp regex ) order by LENGTH(regex) desc;
+----+-------------+-----------------------+------+---------------+------+---------+------+-------+-----------------------------+
| id | select_type | table                 | type | possible_keys | key  | key_len | ref  | rows  | Extra                       |
+----+-------------+-----------------------+------+---------------+------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | wp_WP_SEO_Redirection | ALL  | NULL          | NULL | NULL    | NULL | 58295 | Using where; Using filesort |
+----+-------------+-----------------------+------+---------------+------+---------+------+-------+-----------------------------+
1 row in set (0.01 sec)

Using filesort is killer and I find this kind of queries a lot under mysql’s process list.

Would be of help to exclude regexp? I dont need that.

• This topic was modified 6 years, 5 months ago by DrLightman.