Partition on composite key in MySQL

Question

In mysql can I have a composite primary key composed of an auto increment and another field? Also, please critique my “mysql partitioning” logic

To explain further->

I have a query about MySQL partition. I have to partition a table in MySQL, It has one primary key id. I have to partition by date field(non-primary,duplicate entries). Since we cannot partition on duplicate entries, i have created a composite key->(id,date). How can i create partition in this composite key?

Thanks in Advance...

Rick James Rick James · Accepted Answer · 2017-05-23T00:28:17

(This answer assumes InnoDB, not MyISAM. There are differences in the implementation of indexes that make some of my comments incorrect for MyISAM.)

In MySQL, a table's PRIMARY KEY can be composed of multiple fields, including an AUTO_INCREMENT.

The only requirement in MySQL for AUTO_INCREMENT is that it be the first column in some index. Let's look at this example of Posts, where there can be many posts for each user:

PRIMARY KEY(user_id, post_id),
INDEX(post_id)

where post_id is AUTO_INCREMENT, but you could benefit from "clustering" the data by user_id. This clustering would make it more efficient to do queries like

SELECT ... FROM Posts
     WHERE user_id = 1234;

Back to your question...

The "partition key" does not have to be unique; so, I don't understant your "cannot partition on duplicate entries".

INDEX(id, date), if you also have PRIMARY KEY(id), is essentially useless. When looking up by id, the PRIMARY KEY(id) gives you perfect access; adding date to an index won't help. When looking up by date, but not id, (id, date) is useless since only the "left" part of a composite index can be used.

Perhaps you are leading to a non-partitioned table with

PRIMARY KEY(date, id),
INDEX(id)

to make date ranges efficient? (Note: partitioning won't help.)

Perhaps you will be doing

SELECT ... WHERE x = 123 AND date BETWEEN ...

In that case this is beneficial:

INDEX(x, date)

Only if you do this can we begin to discuss the utility of partitioning:

WHERE x    BETWEEN ...
  AND date BETWEEN ...

This needs a "two-dimensional" index, which sort of exists with SPATIAL.

See my discussion of partitioning where I list only 4 use cases for partitioning. It also links to an a discussion on how to use partitioning for 2D.

Bottom Line: You must not discuss partitioning without having a clear picture of what queries it might help. Provide them; then we can discuss further.

Partition on composite key in MySQL

1 Answers