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
