MySQL Index Selection

[rkondner]

Hi,
Forum Newbie with MyTable Question:

If I have a table with multiple indexes, say indexes on Col_1 and Col_2, and if I use a .Locate method on Col_2 will MySQL automatically use the proper index?

I noticed there was no IndexName property but I did see Index Fields. I would think the RDBMS would take care of such issues. Is this correct?

Thanks,
Bob K

[ViktorV]

If you use the Locate method on Col_2, MySQL cannot use the index. If a table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. MySQL cannot use the index to perform lookups, because Col_2 do not form a leftmost prefix of the index.
More details about how MySQL uses indexes can be found at http://dev.mysql.com/doc/refman/5.0/en/ ... dexes.html