Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Question 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?

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •