MySQL execute time

Question

I need some help cutting the execution time on this query. 7 seconds seems too long for a table with 1500 rows.

  SELECT parent
    FROM video
   WHERE parent NOT IN (SELECT parent
                          FROM video
                         WHERE filename REGEXP '(s[0-9]{2}\e[0-9]{2})|([[:<:]][0-9]{3}[[:>:]])')
GROUP BY parent

Does the column parent have an index? Using regex in a query is probably also a reason why its slow. — datasage
– datasage, Commented Jun 28, 2011 at 2:02

Francois Deschenes · Accepted Answer · 2011-06-28 02:29:56Z

5

First, you can use NOT REGEXP instead of a subquery.

SELECT parent
FROM video
WHERE filename NOT REGEXP '(s[0-9]{2}\e[0-9]{2})|([[:<:]][0-9]{3}[[:>:]])'
GROUP BY parent

When using REGEXP or NOT REGEXP, the indexes don't matter. To make it more efficient, if that's still not enough, you'll have to look at using other string functions or the LIKE operator.

edited Jun 28, 2011 at 2:29

answered Jun 28, 2011 at 2:05

Francois Deschenes

25.1k4 gold badges67 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

el_pup_le Over a year ago

Would this regex be faster with java? I did have everything being parsed through java with regex leaving clean data for my database, but with this new method using regex with queries, I've reduced the amount of code i need by about 50%..

OMG Ponies Over a year ago

@aLk: No, Java will never scale as well as the database can to large data sets.

Francois Deschenes Over a year ago

@aLk - You could always pull all of the data in Java and use it's functions to sort the data. My advice is try using a single query (using NOT REGEXP) and do the same with all of the data using Java and time the execution. If you can run your script through a command line, the time(2) command might help.

Francois Deschenes · Accepted Answer · 2011-06-28 02:09:26Z

2

Two quick things:

Ensure the parent column is indexed.
Drop the regular expression. If you can, use LIKE or some other string function.

edited Jun 28, 2011 at 2:09

Francois Deschenes

25.1k4 gold badges67 silver badges61 bronze badges

answered Jun 28, 2011 at 2:03

Jason McCreary

73.3k23 gold badges140 silver badges177 bronze badges

Collectives™ on Stack Overflow

MySQL execute time

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related