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