Multi column indexes are a powerful way to speed up queries but they are often misunderstood. In most other databases an index on columns a, b, and c can only be used when searching on columns (a,b,& c), (a & b), and (a) -- according to the manual. Also that index supposedly can not be used to search for (b & c) or just (c). Well, that is the way I learned it and the way I have been teaching it. But I was wrong! Now would be a good time to read the MySQL manual on Multiple-Column Indexes as it does not work as noted (or see the excerpt below) and I assumed MySQL worked the same way as the other databases. Well, it doesn't!
Doubt me? Well, lets create table and add in some data.
Table and Data
SQL > create table abcd (a serial …[Read more]