Fast up my mysql query width REGEXP

Question

I have made sql query to compare data

SELECT 
    title
FROM
    links_forum
WHERE
    title REGEXP '[[:<:]]The grey[[:>:]].*[[:<:]](cam|ts|divx|mkv|xvid|dvd|dvdr|dvdrip|brrip|br2dvd|r5|r6|x264|ts2dvd|dvd5|dvd9|720p|1080p)[[:>:]]';

it takes now like 150 sec to excecute

Is there a faster way ?

possible duplicate http://stackoverflow.com/questions/9629488/mysql-regexp-much-slow-than-like — lordkain, Sep 27 '13 at 13:25

score 1 · Accepted Answer · answered Sep 27 '13 at 13:34

The use of the REGEXP matching operator to search defeats using an index scan on the title column to find your content. You may still get some benefit from an index on title because you're only retrieving that one column, so try that if you haven't done so already.

REGEXP is also not the fastest of text matching algorithms. You might get a benefit from trying

   SELECT title
     FROM links_forum
    WHERE title LIKE '%The grey%'
      AND title REGEXP (your big regular expression)

This will still defeat the use of any index, but it will search the title column faster for what I assume is the most important part of your search term. LIKE is a little faster than REGEXP.

Another choice: Instead of searching a big blob of text for something which appears to be video media, you might consider creating a separate table with video media titles in it.

Specifically, you might create a links_forum_media_title table, with links_forum_id and title columns in it. Then when you insert entries in to links_forum you'll also insert entries into this particular table. Then you can create a (title, links_forum_id) index on that table and use it to look up your titles. That's a programming change. But it will solve your performance problem definitively. If you're planning to scale this application up, that will be good.

You could try FULLTEXT searching. But it's not great for this kind of application where you look for some kind of media format code (cam, ts, divx).

score 0 · Answer 2 · answered Sep 27 '13 at 13:24

I believe this query may only be optimized by a small margin. Maybe what you need is to optimize your database and have some other way to know if a given entry is of any of the types specified. For instance add some field saying whether a given entry is video or something like that.

Fast up my mysql query width REGEXP

2 Answers2