Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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"
share|improve this question

1 Answer 1

up vote 1 down vote accepted

You should create an index that involves lastname and maybe state,lastname.

Think of it. What would obligate a Query Optimizer to plan a query to search by lastname within a partition? The only course of action would be a full table scan within the correct partition.

Let's assume 50 U.S. States (no Puerto Rico, Guam, US Commonwealths, or Protectorates)

If you have 1 million rows, that would be average out 20,000 rows per state. A table scan of 20,000s for a lastname may not be that bad. However, we are talking about the United States. Huge numbers could live in New York, California, Texas and Florida. Smaller numbers will reside in Rhode Island, Idaho, Wyoming, and Montana. Search times via full table scans will vary greatly. In light of this, it would be beneficial to index names within each state.

I suggest two indexes

  • LastName : The second index will get the Query Optimizer to search for LastName in every partition. Doing 50 index lookups would sure beat a 50 full table scans any given day. You mentioned you have an index on phonenumber only and the performance was great. The same would have to apply to a LastName-only index.
  • State,LastName : The first index will get the Query Optimizer to search for LastName only down one partition, the state you selected (Maybe unnecessary if the Query Optimizer is intelligent enough to examine the query and select the correct partition).

UPDATE 2012-10-09 16:21 EDT

In all fairness to MySQL's partitioning, if the Query Optimizer is smart enough to navigate data retrieval from just one partition base on the WHERE clause, then indexing (state,lastname) may be unnecesaary. Nevertheless, you must still index on lastname. That way, each partition is indexing on lastname.

My original suggestion of indexing on (state,lastname) is actually based on how I used to index MyISAM tables that were combined under the MERGE storage engine.

Here were my past examples on MERGE tables:

Since Table Partitioning is implemented, indexing on lastname should suffice for you. In fact, recall you stated that the phonenumber is indexed and performance was great, So, I say for the second time, indexing lastname should also be as good for you.

share|improve this answer
    
The query optimizer should know to look in the New York partition (it's partitioned by state, so each partition only has all the people in that one state) if I do SELECT * WHERE lastname="Smith" and state="NY" because I specify NY, right? –  tiredofcoding Oct 8 '12 at 4:48
    
Once the NY partition is selected, there is no other option but a full table scan within that partition. New York is a pretty big state. This is why the first index I suggested makes the most sense. Performance will vary from state to state as I already stated. –  RolandoMySQLDBA Oct 8 '12 at 6:05
    
I guess this is what's confusing me: What if I had 50 tables, one for each state...and in each table I indexed the last name. All I would have to do in that case is SELECT * FROM newyorktable WHERE lastname="Smith" ...isn't it the same thing here with partitions? I was under the impression a partition is like having smaller subtables. –  tiredofcoding Oct 8 '12 at 9:13
    
If you have a table partitioned by state and you have indexes that do not start with state, think about what you are asking the Query Optimizer to do. It would have no choice but to do 50 table scans. By indexing on state, the Query Optimizer can quickly dismiss 49 tables from the execution of the query. By indexing on state and lastname, not only do you have the correct partition selected, but you have the added bonus of an indexed lastname lookup within that partition. Partitioning a table without proper indexing just spreads data that must be searched anyway. –  RolandoMySQLDBA Oct 9 '12 at 17:40
    
If you index on lastname only, you will get 50 indexed lookups for lastname followed by a WHERE clause evaluation for the state NY in every partition. Such a WHERE clause would fail in 49 partitions. –  RolandoMySQLDBA Oct 9 '12 at 17:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.