If you do that, you double the amount of data that has to be stored in memory ;) Anyway. The rule of thumb is to have 'left-most-indexes', which usually means, you can group indexes together, as long as you specify the fields in SELECT and WHERE in the order they appear in the index. You can leave out the fields that are right most and still have the advantage of your index for the left-most fields. Also, don't forget to set the amount of memory used to actually store the index in memory is set high enough in my.cnf. for innoDB you can set innodb_buffer_pool_size can be more than half of your available RAM. But I guess for your shared server setup, this will only give you a vague improvement, unless PLAZA really is the main user of the server. Anyway :) On Fri, 2 Jul 2010, Michiel Van Bel wrote:
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
-- ====================================================================== 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 ======================================================================