LinuxQuestions.org - MYSQL: SQL query takes 10 minutes to execute. Need help optimizing.

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - MYSQL: SQL query takes 10 minutes to execute. Need help optimizing. (https://www.linuxquestions.org/questions/programming-9/mysql-sql-query-takes-10-minutes-to-execute-need-help-optimizing-679717/)

MYSQL: SQL query takes 10 minutes to execute. Need help optimizing.

Using MySQL version 5.0.67 running on a 2.40GHz dual core Intel CPU.

I have the following table:

Code:

CREATE TABLE `files` (

  `name` varchar(250) NOT NULL,

  `type` char(1) NOT NULL,

  `size` bigint(20) NOT NULL,

  `date` datetime NOT NULL,

  `md5` char(32) default NULL,

  `diskid` int(11) default NULL,

  KEY `md5index` (`md5`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

It contains 28573 rows.

md5 is the md5sum of the files.

Now, some of the files are identical.

Code:

SELECT count(*), md5 

FROM files 

WHERE md5 IS NOT NULL

GROUP BY md5 

HAVING count(*) > 1;

Mysql says 212 rows in set (0.03 sec)

Say I want to retrieve the name of all files which has more than 3 duplicate md5

Code:

SELECT name, date, md5 

FROM files 

WHERE md5 IN 

  (SELECT md5 

  FROM files 

  WHERE md5 IS NOT NULL 

  GROUP BY md5 

  HAVING count(*) > 3

  );

This query takes 10 minutes to execute. The subquery returns 33 rows in set (0.02 sec) and the entire query returns 334 rows in set (10 min 4.01 sec)

This is obviously a bad query. Reading the entire table takes 0.10 sec.

In Sybase this is rather easy because I can avoid the subquery, but as far as I know MySQL would not work this way. The same applies to MSSQL.

Any suggestion on how to improve this query?

Quote:

Originally Posted by robel (Post 3324779)

Using MySQL version 5.0.67 running on a 2.40GHz dual core Intel CPU.

I have the following table:

Code:

CREATE TABLE `files` (

  `name` varchar(250) NOT NULL,

  `type` char(1) NOT NULL,

  `size` bigint(20) NOT NULL,

  `date` datetime NOT NULL,

  `md5` char(32) default NULL,

  `diskid` int(11) default NULL,

  KEY `md5index` (`md5`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

It contains 28573 rows.

md5 is the md5sum of the files.

Now, some of the files are identical.

Code:

SELECT count(*), md5 

FROM files 

WHERE md5 IS NOT NULL

GROUP BY md5 

HAVING count(*) > 1;

Mysql says 212 rows in set (0.03 sec)

Say I want to retrieve the name of all files which has more than 3 duplicate md5

Code:

SELECT name, date, md5 

FROM files 

WHERE md5 IN 

  (SELECT md5 

  FROM files 

  WHERE md5 IS NOT NULL 

  GROUP BY md5 

  HAVING count(*) > 3

  );

one suggestion that might help, though i'm not sure, :-(
as far as my knowlegde is concerned, at teh lower level the query gets optimized and what i can say in your case is that this optimization is the default optimization available, so it might help a bit if you can provide your own procedures thereby asking the underlying "query manager"
to do a customised optimization. do think in this direction and let us know your views/issues etc. thank you.
lets see what other ppl/experts in this field have to say.

Hello, this is odd but it would make sense that it is executing that same sub-query 28,573 times. Here is my reasoning:
0.02s * 28,573 = 571.46s
570.46s * (1m/60s) = 9.52m
Note: 's' is seconds and 'm' is minutes

This is odd because normally databases will see that a sub-query is not dependent on any value in the outer query and will internally optimize the query so that it only runs the sub-query once. The only thing I can think of is that MySQL is seeing the same table and column names in the outer and inner queries and thinks it must run the inner query ever time because it may depend on the outer one. To test this, give specific names to the inner and outer query tables like so:

Code:

SELECT outer.name, outer.date, outer.md5 

FROM files AS outer

WHERE outer.md5 IN 

  (SELECT inner.md5 

  FROM files AS inner

  WHERE inner.md5 IS NOT NULL 

  GROUP BY inner.md5 

  HAVING count(*) > 3

  );

If the above query drastically reduces the query time, then I would speculate that what I said above is the problem.

Otherwise, we will have to play around with indexes and/or possibly find a better way to write the query.

What version of MySQL are you using?

Quote:

Originally Posted by CRC123 (Post 3324983)

Yeah, that makes sense even though it's odd.

Code:

mysql> SELECT outer.name, outer.date, outer.md5

    -> FROM files AS outer

    -> WHERE outer.md5 IN

    ->  (SELECT inner.md5

    ->    FROM files AS inner

    ->    WHERE inner.md5 IS NOT NULL

    ->    GROUP BY inner.md5

    ->    HAVING count(*) > 3

    ->  );

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer

WHERE outer.md5 IN

  (SELECT inner.md5

  FROM files AS inner

  WHERE i' at line 2

Guess I have to check the manual. :)

Quote:

Originally Posted by CRC123 (Post 3324983)

What version of MySQL are you using?

5.0.67

Quote:

Originally Posted by robel (Post 3325198)

Yeah, that makes sense even though it's odd.

Code:

mysql> SELECT outer.name, outer.date, outer.md5

    -> FROM files AS outer

    -> WHERE outer.md5 IN

    ->  (SELECT inner.md5

    ->    FROM files AS inner

    ->    WHERE inner.md5 IS NOT NULL

    ->    GROUP BY inner.md5

    ->    HAVING count(*) > 3

    ->  );

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer

WHERE outer.md5 IN

  (SELECT inner.md5

  FROM files AS inner

  WHERE i' at line 2

Guess I have to check the manual. :)

5.0.67

LOL :), both 'outer' and 'inner' are SQL reserved words. They are used in join syntax's. Switch those keyboards to something else. Ex:

Code:

SELECT t1.name, t1.date, t1.md5 

FROM files AS t1

WHERE t1.md5 IN 

  (SELECT t2.md5 

  FROM files AS t2

  WHERE t2.md5 IS NOT NULL 

  GROUP BY t2.md5 

  HAVING count(*) > 3

  );

Sorry about that, the above syntax should work as long as you don't use reserved words as Aliases. ;)

Quote:

Originally Posted by CRC123 (Post 3325235)

LOL :), both 'outer' and 'inner' are SQL reserved words. They are used in join syntax's.

Yes, it was my first thought. Seems to work now.

That is, the syntax is ok, but still it takes long time to execute. I have waited a few minutes now. Still no results. Oh, and the mysqld process is in 100% CPU utilization. It was a nice try and a plausible explanation. I still think you're explanation is close to true, but insinuating a MySQL bug is probably not wise... :scratch:

Is there an alternative query to get the same results? I was pondering about a JOIN but couldn't figure out how to do it.

Edit: Here is the result: 334 rows in set (10 min 4.24 sec)

Quote:

Originally Posted by harry2006 (Post 3324948)

Perhaps by saving the subquery in a temporary table and join the two may help? I'll give it a try... Later. Now I have to get som sleep. Local time in Norway is 22:18 and I have to get up early. :)

Got it! Thanks to all for guiding me to the answer. I've never come up with this alone.

Code:

SELECT files.name, files.date, files.md5  FROM files

INNER JOIN  

(SELECT md5 FROM files 

 WHERE md5 IS NOT NULL

 GROUP BY md5

 HAVING count(*) > 3 ) AS x

ON files.md5=x.md5;

334 rows in set (0.02 sec)

It's really cooking with gas! (It's all electrical of course, but then it's just a saying right? http://en.wiktionary.org/wiki/cooking_with_gas) :D

The clue was using a JOIN in the query.