Is there a better way to index multiple columns than creating an index for each permutation?
database, indexing, mysql
Solution
In MS SQL the index "a, b, c" will cover you for scenarios "a"; "a, b"; and "a, b, c". So you would only need the following indexes:
a, b, c
b, c
c
Not sure if MySQL works the same way, but I would assume so.
Problem
Suppose I have a database table with columns a, b, and c. I plan on doing queries on all three columns, but I'm not sure which columns in particular I'm querying. There's enough rows in the table that an index immensely speeds up the search, but it feels wrong to make all the permutations of possible indexes (like this): ``` a b c a, b a, c b, c a, b, c ``` Is there a better way to handle this problem? (It's very possible that I'll be just fine indexing a, b, c alone, since this will cut down on the number of rows quickly, but I'm wondering if there's a better way.) If you need more concrete examples, in the real-life data, the columns are city, state, and zip code. Also, I'm using a MySQL database.