[drupal-devel] MySQL indices not being picked up
danielc at analysisandsolutions.com
Sun May 15 13:58:41 UTC 2005
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
select * from foo where one = 11, two = 22
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
select * from foo where two = 22, three = 33
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?
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
4015 7th Ave #4, Brooklyn NY 11232 v: 718-854-0335 f: 718-854-0409
More information about the drupal-devel