Hi, Sorry finally there is no lock with 'use database', it only takes a huge amont of time because of the number of table in my database :)
Regards, Jocelyn ----- Original Message ----- From: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; "Heikki Tuuri" <[EMAIL PROTECTED]> Sent: Monday, April 15, 2002 1:35 PM Subject: Re: How does DISTINCT really work ? > Hi, > > I have the latest 4.0.2 source installed, and the problem is still here > AFAIK : > > mysql> \s > -------------- > mysql Ver 12.1 Distrib 4.0.2-alpha, for pc-linux-gnu (i686) > > -=[root@anedroide]=(/usr/local/mysql-4.0)# bk export -tpatch > <(13:29:52) > # This is a BitKeeper generated patch for the following project: > # Project Name: MySQL - fast and reliable SQL database > # This patch format is intended for GNU patch command version 2.5 or higher. > # This patch includes the following deltas: > # ChangeSet 1.1235 -> 1.1236 > # Docs/manual.texi 1.865 -> 1.866 > # > # The following is the BitKeeper ChangeSet Log > # -------------------------------------------- > # 02/04/15 [EMAIL PROTECTED] 1.1236 > # Documentation fix. > # -------------------------------------------- > > mysql> SELECT DISTINCT topic FROM > forum.searchmainpresencepc4,forum.searchjoinpresencepc4 WHERE > searchmainpresencepc4.numreponse=searchjoinpresencepc4.numreponse AND > (mot='les') ORDER BY date DESC LIMIT 0,40; > +-------+ > | topic | > +-------+ > | 11462 | > <cut> > | 12313 | > | 9673 | > | 11462 | > <cut> > | 12282 | > | 9673 | > | 12224 | > +-------+ > 40 rows in set (0.06 sec) > > > BTW I have a problem with the latest mysql client (12.1) : > > It seems to be locked when I make a 'use database' : > > > mysql> use forum > Reading table information for completion of table and column names > You can turn off this feature to get a quicker startup with -A > > <locked> > > Regards, > > Jocelyn > ----- Original Message ----- > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Monday, April 15, 2002 1:06 PM > Subject: Re: How does DISTINCT really work ? > > > > Hi! > > > > Ok, Monty said that this DISTINCT ... ORDER BY bug has already been fixed > in > > 4.0. > > > > Regards, > > > > Heikki > > Innobase Oy > > > > ----- Original Message ----- > > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > > To: "SDiZ (UHome)" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; > > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > Sent: Monday, April 15, 2002 9:06 AM > > Subject: Re: How does DISTINCT really work ? > > > > > > > Hi! > > > > > > ----- Original Message ----- > > > From: "SDiZ (UHome)" <[EMAIL PROTECTED]> > > > To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > > Sent: Monday, April 15, 2002 7:51 AM > > > Subject: Re: How does DISTINCT really work ? > > > > > > > > > > I think every thing are documented. > > > > > > > > http://www.mysql.com/doc/B/u/Bugs.html > > > > a.. 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 doesn'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 > > > the > > > > this only happens for queries where you don't have the ORDER BY > > > > columns in the result, something that is you are not allowed to do in > > ANSI > > > > SQL. > > > > > > Ok, thank you for noting this! > > > > > > We should update the manual so that it mentions this exception to the > rule > > > on the proper page http://www.mysql.com/doc/S/E/SELECT.html > > > > > > And maybe also change the processing of DISTINCT so that there is no > such > > > exception to the rule. > > > > > > Regards, > > > > > > Heikki > > > Innobase Oy > > > > > > > ----- Original Message ----- > > > > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > > > > To: <[EMAIL PROTECTED]> > > > > Sent: Monday, April 15, 2002 2:24 AM > > > > Subject: Fw: How does DISTINCT really work ? > > > > > > > > > > > > > Forwarded from the general mailing list. > > > > > > > > > > Mail filter bait: > > > > > > > > > > How-To-Repeat > > > > > > > > > > ----- Original Message ----- > > > > > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > > > > > To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > > > > Cc: <[EMAIL PROTECTED]> > > > > > Sent: Sunday, April 14, 2002 9:14 PM > > > > > Subject: Re: How does DISTINCT really work ? > > > > > > > > > > > > > > > > Jocelyn, > > > > > > > > > > > > the below shows a bug in the MySQL DISTINCT operation. If you > > specify > > > > > > DISTINCT, you should not get duplicate rows. > > > > > > > > > > > > I have forwarded this email to Monty. > > > > > > > > > > > > Have you tried myisamcheck on the tables? > > > > > > > > > > > > If the tables are small, could you please upload them to the > > 'secret' > > > > > > directory in the MySQL support ftp server. I do not remember the > > > > directory > > > > > > exactly, but you have reported so many bugs in MySQL that maybe > you > > > > > remember > > > > > > :). > > > > > > > > > > > > Best regards, > > > > > > > > > > > > Heikki Tuuri > > > > > > Innobase Oy > > > > > > --- > > > > > > Order technical MySQL/InnoDB support at https://order.mysql.com/ > > > > > > See http://www.innodb.com for the online manual and latest news on > > > > InnoDB > > > > > > > > > > > > > > > > > > ----- Original Message ----- > > > > > > From: ""DL Neil"" <[EMAIL PROTECTED]> > > > > > > Newsgroups: mailing.database.mysql > > > > > > Sent: Sunday, April 14, 2002 8:01 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]> > > > > > > > > > > > > > > > > > > > > > --------------------------------------------------------------------- > > 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]> > > > > > > > --------------------------------------------------------------------- > 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]> > > --------------------------------------------------------------------- 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
