Unanswered: Do you need to index the key you're partitioning on?
I have an InnoDB table of customers, partitioned by KEY, with each partition being one US state. I have an index on phone number, and the performance is great. We also need to SELECT by last name in a given US state. Should I create an index on lastname AND state, or there's no point in doing that because the table is already partitioned by state?
Will that take up unnecessary space, since InnoDB should already know what partition is which state, and it only has to find all rows matching last name in that partition?
We aren't doing any joins whatsoever...this is just for a simple query like this:
SELECT * FROM table WHERE lastname = "Smith" AND state="NY"
To me, that suggests mysql should know to look only in the NY partition if I have an index on lastname. Should I also index state?
As per the documentation: "Partitioning applies to all data and indexes of a table; you cannot partition only the data and not the indexes, or vice versa, nor can you partition only a portion of the table."
This means that each index is local to the partition. So it will use state to identify the partition and then use the index inside that partition to get the data.