Okay, I now understand the problem we (PLAZA dudes) are having with the database indices. http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html Apparently indices cannot be combined when using "LIKE" queries. Thus, if you have a table with 2 columns containing gene identifiers, and you want to find the Arabidopsis genes (which start with AT), you get this: * SELECT * FROM `table` WHERE `gene_x` LIKE 'AT%' o uses index on column `gene_x` * SELECT * FROM `table` WHERE `gene_x` LIKE 'AT%' OR `gene_y` LIKE 'AT%' o no indices used * SELECT * FROM `table` WHERE `exp_id` = '1' AND (`gene_x` LIKE 'AT%' OR `gene_y` LIKE 'AT%') o Index on column exp_id used. * SELECT * FROM `table` WHERE `exp_id` = '1' AND `gene_x` LIKE 'AT%' o Only index on column exp_id is used However, if I use a join on another table to get the species of the genes, then multiple indices can and will indeed be used. I really don't remember my courses on databases covering these things :-( Michiel Kenny Billiau wrote:
For double-indexes you can perform following queries that will result in using the index:
* match full value * match leftmost prefix (what I tried to explain) e.g. Match a full last_name. * match column prefix e.g. Match all last_names starting with 'A%'. * match a range of values e.g. match all last_names between Billiau and Van Bel * match one part exactly and a range on antoher part e.g. match last_name exact and and a range query on firstname. This is simply an extension of the leftmost prefix. * covering index query e.g. you only query and SELECT last_name and first_name. this is lightning fast as all the info is stored in memory anyway.
Again, rule of thumb is, match from left to right ;)
hh, Kenny
On Thu, 15 Jul 2010, Michiel Van Bel wrote:
okay, I'm a bit confused: http://dev.mysql.com/doc/refman/4.1/en/multiple-column-indexes.html
You see 2 examples at the bottom, where the name index is NOT used. I understand why the index is not used in the first example, but why is it not used for the "last_name" part in the query of the second example?
I think I need to revise my knowledge on indexing a bit :-/
-- ====================================================================== 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 ==================================================================