In fact what it's odd is when I use EXPLAIN, I don't see anywhere DISTINCT in the EXPLAIN result :
mysql> EXPLAIN SELECT DISTINCT topic FROM searchmainhardwarefr7,searchjoinhardwarefr7 WHERE searchmainhardwarefr7.numreponse=searchjoinhardwarefr7.numreponse AND date >='2002-03-24' AND (mot='test') ORDER BY date DESC LIMIT 0,20; +-----------------------+--------+--------------------+------------+-------- -+----------------------------------+------+-------------------------------- --------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-----------------------+--------+--------------------+------------+-------- -+----------------------------------+------+-------------------------------- --------------------------+ | searchmainhardwarefr7 | ref | PRIMARY,numreponse | PRIMARY | 30 | const | 3597 | where used; Using index; Using temporary; Using filesort | | searchjoinhardwarefr7 | eq_ref | PRIMARY,numreponse | numreponse | 4 | searchmainhardwarefr7.numreponse | 1 | where used | +-----------------------+--------+--------------------+------------+-------- -+----------------------------------+------+-------------------------------- --------------------------+ 2 rows in set (0.00 sec) However if I remove the AND mot='test', DISTINCT comes back in the EXPLAIN result : mysql> EXPLAIN SELECT DISTINCT topic FROM searchmainhardwarefr7,searchjoinhardwarefr7 WHERE searchmainhardwarefr7.numreponse=searchjoinhardwarefr7.numreponse AND date >='2002-03-24' ORDER BY date DESC LIMIT 0,20; +-----------------------+-------+--------------------+------------+--------- +----------------------------------+--------+------------------------------- ---------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-----------------------+-------+--------------------+------------+--------- +----------------------------------+--------+------------------------------- ---------------------------+ | searchjoinhardwarefr7 | range | PRIMARY,numreponse | PRIMARY | 8 | NULL | 174020 | where used; Using index; Using temporary; Using filesort | | searchmainhardwarefr7 | ref | numreponse | numreponse | 4 | searchjoinhardwarefr7.numreponse | 9 | Using index; Distinct | +-----------------------+-------+--------------------+------------+--------- +----------------------------------+--------+------------------------------- ---------------------------+ 2 rows in set (0.00 sec) Jocelyn ----- Original Message ----- From: "DL Neil" <[EMAIL PROTECTED]> To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, April 14, 2002 6:58 PM Subject: Re: How does DISTINCT really work ? > Hi Jocelyn, > > I don't have a quick/sure answer for you, but am wondering about the > relationship between the date and topic columns - I note that the '429s > are interspersed by other values. > > I'm a great believer in the KISS principle (simple things amuse simple > minds!) > May I suggest taking out the last three clauses (ORDER BY, DESC, and > LIMIT) and trying that. > Then if it works, put the clauses back, one at a time (from left to > right), observing the effect. > > Please let me know how you get on, > =dn > > > > So why does I obtain duplicates numbers for 'topic' with the first > query > > with DISTINCT ? (perhaps a bug ?) > > > > Regards, > > > > Jocelyn Fournier > > > > ----- Original Message ----- > > From: "DL Neil" <[EMAIL PROTECTED]> > > To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; > > <[EMAIL PROTECTED]> > > Sent: Sunday, April 14, 2002 5:54 PM > > Subject: Re: How does DISTINCT really work ? > > > > > > > Hi Jocelyn, > > > > > > > I wonder how works DISTINCT when using it with the following join. > > > > Ex : > > > > > > > > mysql> SELECT DISTINCT topic FROM > > > > searchmainhardwarefr7,searchjoinhardwarefr7 WHERE > > > > searchmainhardwarefr7.numreponse=searchjoinhardwarefr7.numreponse > AND > > > > (mot='test') AND date >= '2002-03-24' ORDER BY date DESC LIMIT > 0,20; > > > > +--------+ > > > > | topic | > > > > +--------+ > > > > | 108499 | > > > > | 108341 | > > > > | 108425 | > > > > | 108425 | > > > > | 108425 | > > > > | 108429 | > > > > | 108429 | > > > > | 108429 | > > > > | 108429 | > > > > | 108427 | > > > > | 108427 | > > > > | 108429 | > > > > | 108429 | > > > > | 108429 | > > > > | 108429 | > > > > | 108429 | > > > > | 108429 | > > > > | 108429 | > > > > | 108429 | > > > > | 108429 | > > > > +--------+ > > > > 20 rows in set (0.08 sec) > > > > > > > > In this case I was expecting only distinct topic number list, like > > > with the > > > > following query : > > > > > > > > mysql> SELECT topic FROM > searchmainhardwarefr7,searchjoinhardwarefr7 > > > WHERE > > > > searchmainhardwarefr7.numreponse=searchjoinhardwarefr7.numreponse > AND > > > > (mot='test') AND date >= '2002-03-24' GROUP BY topic ORDER BY date > > > DESC > > > > LIMIT 0,20; > > > > +--------+ > > > > | topic | > > > > +--------+ > > > > | 108499 | > > > > | 108429 | > > > > | 108427 | > > > > | 108425 | > > > > | 108417 | > > > > | 108341 | > > > > | 108312 | > > > > | 108303 | > > > > | 108275 | > > > > | 108268 | > > > > | 108167 | > > > > | 108233 | > > > > | 108231 | > > > > | 108172 | > > > > | 108052 | > > > > | 108102 | > > > > | 107835 | > > > > | 108122 | > > > > | 108096 | > > > > | 106794 | > > > > +--------+ > > > > 20 rows in set (0.10 sec) > > > > > > > > Does DISTINCT implicitely check columns included in the WHERE > clause ? > > > > Thanks you. > > > > > > > > > AFAIK the WHERE clause (join) is carried out earlier (etc) and the > > > DISTINCT carried out on the resultset, almost at the end of the > process. > > > > > > Regards, > > > =dn > > > > > > > > > > --------------------------------------------------------------------- > > > 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 > > --------------------------------------------------------------------- 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
