Hi!
>>>>> "Sinisa" == Sinisa Milivojevic <[EMAIL PROTECTED]> writes:
Sinisa> Fournier Jocelyn [Presence-PC] writes:
>> Hi,
>>
>> Yes it is 4.0.1.
>> But the first query has also to make a temporary table with 2 million rows,
>> it's why I don't understand the delta between the query without DISTINCT and
>> the query with DISTINCT.
>> The "remove duplicates" doesn't occurs after the join was performed ?? (it
>> should be really fast in this case)
>>
>> Regards,
>>
>> Jocelyn
Sinisa> DISTINCT simply has to re-iterate.
I will try to explain what happens in this case:
mysql> SELECT DISTINCT COUNT(*) as count, numreponse FROM searchhardwarefr3
GROUP BY mot,date,numreponse HAVING count>1 LIMIT 100;
| searchhardwarefr3 | index | NULL | PRIMARY | 75 | NULL |
2026032 | Using index; Using temporary |
MySQL has 4 different way to optimize DISTINCT queries.
This is the worst case for the distinct optimization for MySQL and we
really have to fix this in the near future. The worst case is when:
- You are doing a DISTINCT on a GROUP BY and the result set doesn't
fit into a HEAP temporary table (tmp_table_size) and the result
set is larger than sort_buffer_size.
In this case MySQL does the following loop:
- Iterate through all rows in
- Iterate through all rows after the current one in the temporary table
- Delete the row in the inner iteration if the rows are the same
- Sort rows
- Send them to the client.
(Yes, I know that his is a hack, but this is not a common query...)
The right way to fix this would be to create an unique index over the
result rows, and let the REPAIR code regenerate the index, removing
duplicate rows as it finds them.
We will put this in our TODO to get this fixed in the near future.
Regards,
Monty
---------------------------------------------------------------------
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
