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%'

uses index on column `gene_x`

SELECT * FROM `table` WHERE `gene_x` LIKE 'AT%' OR `gene_y` LIKE 'AT%'

no indices used

SELECT * FROM `table` WHERE `exp_id` = '1' AND (`gene_x` LIKE 'AT%' OR `gene_y` LIKE 'AT%')

Index on column exp_id used.

SELECT * FROM `table` WHERE `exp_id` = '1' AND `gene_x` LIKE 'AT%'

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
==================================================================