6

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"
Improve this question
1
  • Your particular case should be covered by the partitioning itself but many cases won't be. For example count(*) where state='NY' will need an index or will do a fulltable scan (of the partition).
    – John
    Commented Jul 24, 2018 at 21:39

2 Answers 2

Reset to default
2

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.

Improve this answer
8
  • 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
    Commented Oct 8, 2012 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
    Commented Oct 8, 2012 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
    Commented Oct 8, 2012 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
    Commented Oct 9, 2012 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
    Commented Oct 9, 2012 at 17:42
1

Without partitioning

Based on what you said, PARTITIONing is useless for your table. Instead, have a non-partitioned table with

INDEX(phonenumber),
INDEX(lastname, state) -- in either order

They would be optimal for your two queries. The non-partitioned lookup by phonenumber would be better because it need look only one place, not 50 (plus merge the results, 49 of which are empty).

With PARTITION BY KEY(state)

INDEX(phonenumber), -- checks 50 places
INDEX(last_name)  -- sufficient...

WHERE lastname = "Smith" AND state="NY" will first "prune" to the one partition, then search the index specific to the NY partition. Adding state to the index (either first or last) would provide no advantage, and make the index larger (a slight disadvantage).

BTW, California has 12% of the population of the US; Wyoming has 0.2%. Such imbalance has minimal impact because its BTrees may not be any deeper.

Each partition has a bunch of "free" space; the overall free space for the table is about 50 times as much as with a non-partitioned table. For that reason alone, don't bother with partitioning unless you have at least a million rows.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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