Just spent an hour with this, so I thought I should share. I will paste the stuff from mysql site rather then trying to explain it myself


The optimiser may handle DISTINCT differently if you are using 'hidden' columns in a join or not. In a join, hidden columns are counted as part of the result (even if they are not shown) while in normal queries hidden columns don't participate in the DISTINCT comparison. We will probably change this in the future to never compare the hidden columns when executing DISTINCT. An example of this is:


SELECT DISTINCT mp3id FROM band_downloads
WHERE userid = 9 ORDER BY id DESC;

and

SELECT DISTINCT band_downloads.mp3id
FROM band_downloads,band_mp3
WHERE band_downloads.userid = 9
AND band_mp3.id = band_downloads.mp3id
ORDER BY band_downloads.id DESC;

In the second case you may in MySQL Server 3.23.x get two identical rows in the result set (because the hidden id column may differ). Note that this happens only for queries where you don't have the ORDER BY columns in the result, something that you are not allowed to do in SQL-92.


This behaves differently in version 3.x and 4.x. Hope it saves some time to someone.