[drupal-devel] MySQL indices not being picked up

[Daniel Convissor]

Dries:

On Sun, May 15, 2005 at 01:40:30PM +0200, Dries Buytaert wrote:
> Does anyone know why MySQL does not use indices/keys for the following  
> query despite the fact it has plenty of indices to choose from ...

Without looking at this too hard and not having the actual table 
definitions in front of me...  I noticed some of the columns used in the 
joins are part of multi-column indexes.  When querying columns that are in 
a multi-column index, one can't reach a column in the middle without using 
the preceeding columns.  Example:

   create index foo on bar (one, two, three);

If you try to join/select/etc on column "two", the index can't be used.  
The index only gets used if your query involves the prior columns.  The 
key gets used for these:
   select * from foo where one = 11
or
   select * from foo where one = 11, two = 22
or 
   select * from foo where one = 11, two = 22, three = 33

BUT, the index won't get used on these:
   select * from foo where two = 22
or
   select * from foo where two = 22, three = 33
or
   select * from foo where three = 33


So, the order you define the index in is important.  One must to take into 
consideration how the index will be used.  If two columns in the index are 
subjects of frequent queries, but the second column sometimes not used in 
conjunction with the first, a separate index comprised of that column 
alone should be considered.

I hope my explanation is clear.  Perhaps you already knew this.  Is that 
the issue here?

--Dan

-- 
 T H E   A N A L Y S I S   A N D   S O L U T I O N S   C O M P A N Y
            data intensive web and database programming
                http://www.AnalysisAndSolutions.com/
 4015 7th Ave #4, Brooklyn NY 11232  v: 718-854-0335 f: 718-854-0409