You're right, it wasn't the answer I wanted to hear ;-) But it has a ring of truth to it.....
Thanks for all your help Mick Paul DuBois wrote: > At 13:34 +0100 4/19/02, Mick Watson wrote: > >Thank you :-) > > > >I do realise the SQL is not being used as it should be, but as it is not my > >database and nor is it my SQL, there's not much I can do to really change > >either of these.... I merely have to work with a database that has been > >produced by a third party group, and I must say at this point that I have > >nothing but praise for the ensembl group for the difficult job that they have > >done very well (see http://www.ensembl.org) > > > >So, I realise this is not the problem of mySQL, but what I want to try and > >find out is really the internal workings of mySQL when it performs this > >operation so that I can understand why my copy of ensembl, which is produced > >from direct dumps of the main ensembl, behaves differently to the main > >ensembl. > > I guess you won't want to hear this, but that is completely the wrong > approach. You *might* be able to achieve the same query output on both > machines by dumping the database on one machine, then dropping and reloading > the tables on both machines using the same copy of the dump. Then presumably > both tables will have both records loaded in the same order, and the query > *might* produce the same result on both machines. > > - ...until the table gets modified > - ...until MySQL is upgraded on one machine and not the other, and something > happens in the query optimizer that changes the query output > - ...etc. > > The group you're working with *must* change the queries to be correct. > Otherwise, you're entirely at the mercy of any number of external factors > that can change the output and over which you have no control. > > > > >I guess from what you are saying, that the data is stored randomly, that there > >is very little I can do to actually make my database behave the same as the > >main database? Could it be affected in any way by operating system and/or > >file system? Superficially the data is organised in exactly the same way in > >both databases, but I have no doubt that things like memory locations are > >completely different, but possibly if I could understand what the variables > >are that affect this behaviour I could minimise the inconsistancy...? > > > >Thanks for your time > > > >Mick > > > >Richard Emery wrote: > > > >> mysql is acting correctly. > >> > >> GROUP BY is used to consolidate data for SUMming, COUNTing, etc. Your > >> SELECT statement makes not such request. You have simply requested the > >> value of a specific field. Data are stored in mysql databases randomly. > >> Therefore, when you request a field's data, you are getting whatever is > >> first in the list of records matching your WHERE clause. > >> > >> Bottom line: you are NOT using GROUP BY as it is supposed to be used. Your > > > SQL is in error, not mysql. --------------------------------------------------------------------- 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
