Hi, Yes, the reason for the time difference is that for distinct query, as Sinisa noted, it has to reiterate.
For the output to generate, first the rows have to be ordered ( in this case since count(*) is given every column should be present in the comparison.) using a temp table (or any other mechanism to keep rows ordered). For the first query also there should be an intermediate temp table to order rows but here it is only necessary to consider the columns in the group by clause. Considering these, a rough and primitive estimate of the time taken to execute the query can be found. Suppose the average length of a row is 300 bytes and the three columns in the group by clause takes 30 bytes average. Then if the first query takes 15 minutes, the second query will take 150 minutes. This may not be the real scenario with mysql but some thing similar. The having clause I feel, is redundant. the 15 min for the first qurey seem too much, perhaps indexing might help. Hope somebody else has a better explanation. Anvar. At 02:41 AM 18/01/2002 +0100, you wrote: >Hi, > >I've notice sometimes DISTINCT clause take a really high amount of time to >remove duplicates whereas it should be really quick (I assume it should be >;)) > >My first query is : > >mysql> SELECT COUNT(*) as count, numreponse FROM searchhardwarefr3 GROUP BY >mot,date,numreponse HAVING count>1 LIMIT 100; > >it returns : > >+-------+------------+ >| count | numreponse | >+-------+------------+ >| 2 | 111239 | >| 2 | 108183 | >| 2 | 111173 | >| 2 | 111383 | ><cut> >| 2 | 111239 | >| 2 | 111760 | >| 3 | 109166 | >| 2 | 111109 | >| 3 | 109166 | >+-------+------------+ >58 rows in set (14 min 51.15 sec) > >My second query is : > > >mysql> SELECT DISTINCT COUNT(*) as count, numreponse FROM searchhardwarefr3 >GROUP BY mot,date,numreponse HAVING count>1 LIMIT 100; > >Well I'm not enough patient to wait, but when I stop the querie, it has been >running for more than 3500 seconds... (and more than 45mn in 'Removing >duplicates' state...) > >mysql> EXPLAIN SELECT DISTINCT COUNT(*) as count, numreponse FROM >searchhardwarefr3 GROUP BY mot,date,numreponse HAVING count>1 LIMIT 100; >+-------------------+-------+---------------+---------+---------+------+---- >-----+------------------------------+ >| table | type | possible_keys | key | key_len | ref | >rows | Extra | >+-------------------+-------+---------------+---------+---------+------+---- >-----+------------------------------+ >| searchhardwarefr3 | index | NULL | PRIMARY | 75 | NULL | >2026032 | Using index; Using temporary | >+-------------------+-------+---------------+---------+---------+------+---- >-----+------------------------------+ >1 row in set (0.00 sec) > >mysql> EXPLAIN SELECT COUNT(*) as count, numreponse FROM searchhardwarefr3 >GROUP BY mot,date,numreponse HAVING count>1 LIMIT 100; >+-------------------+-------+---------------+---------+---------+------+---- >-----+------------------------------+ >| table | type | possible_keys | key | key_len | ref | >rows | Extra | >+-------------------+-------+---------------+---------+---------+------+---- >-----+------------------------------+ >| searchhardwarefr3 | index | NULL | PRIMARY | 75 | NULL | >2026032 | Using index; Using temporary | >+-------------------+-------+---------------+---------+---------+------+---- >-----+------------------------------+ >1 row in set (0.00 sec) > > >Why does it take so much time to remove duplicates in only 58 rows ?? > >Thank you :) > >Regards, > >Jocelyn Fournier >Presence-PC > > > > > > >--------------------------------------------------------------------- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail ><[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
