this might actually explain some of my performance issues :-/ So the solution is: put an index on all fields of all tables, to be on the safe side :-) Kenny Billiau wrote:
The problem with a group by is that is creates a temporary table. So unless this tmp-table is created fully from a covering index (= retrieval of this index results in the retrieval of all requested information in the SELECT clause), this will be ass slow.
It's vaguely explained here: http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html
But as long as you don't go into the 1M row range, you won't notice anything from this ;)
On Fri, 2 Jul 2010, Sofie Van Landeghem wrote:
Everything is *always* a priority queue!
Kenny Billiau wrote: Hey .. o_O .. what ?
But that would make Francis Marijn 'the buffer underflow'? (wait, Marijn[-1] in perl would just give the last element .. which is Marijn .. so Marijn = Francis, which makes me Francis the second, which actualy makes sense!)
Should I now also explain the drawback of loosing the ability of using indexes with group by for extra points ? Or ain't this a priority queue?
On Fri, 2 Jul 2010, Sofie Van Landeghem wrote:
It's Marijn the 0th to the rescue! :D
Kenny Billiau wrote: If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote: lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de<mailto:billiau@mpimp-golm.mpg.de><mailto:billiau@mpimp-golm.mpg.de><mailto:billiau@mpimp-golm.mpg.de>
Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================
________________________________
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- Sofie Van Landeghem PhD Student VIB Department of Plant Systems Biology, Ghent University Bioinformatics and Systems Biology Technologiepark 927, 9052 Gent, BELGIUM Tel: +32 (0)9 331 36 95 Website: http://bioinformatics.psb.ugent.be
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de<mailto:billiau@mpimp-golm.mpg.de> Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================
________________________________
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- Sofie Van Landeghem PhD Student VIB Department of Plant Systems Biology, Ghent University Bioinformatics and Systems Biology Technologiepark 927, 9052 Gent, BELGIUM Tel: +32 (0)9 331 36 95 Website: http://bioinformatics.psb.ugent.be
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ====================================================================== ------------------------------------------------------------------------
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ================================================================== Michiel Van Bel PhD student Tel:+32 (0)9 331 36 95 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Ghent University Technologiepark 927, 9052 Gent, BELGIUM mibel@psb.vib-ugent.be http://www.psb.vib-ugent.be http://bioinformatics.psb.ugent.be ==================================================================