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
