I
I
inpost2015-05-04 15:33:18
MySQL
inpost, 2015-05-04 15:33:18

How to arrange indexes in MySQL with partition?

Table 1 (message table, binding by id_parent to the correspondence table between A and B):
`id` AUTO_INCREMENT
`id_parent` (binding to another table)
`text` Queries
used:
DELETE FROM `table1` WHERE `id_parent` = 1 AND ` id` = 1
SELECT ... WHERE `id_parent` = 1 ORDER BY `id` DESC
I want to create partitions by `id_parent` field.
If without partitions, then I would create the following indexes:
1) PK `id` (for the first)
2) `id_parent` + `id` (for the second query)
For partitions, you need to combine the AUTO_INCREMENT index together with the column to split, total PK: `id`+`id_parent`. Creating `id_parent` + `id` fails due to the error "Incorrect table definition; there can be only one auto column and it must be defined as a key".
Question: How to create indexes and partitions in this task?
A friend advises to create only one PK `id` + `id_parent`, and rewrite queries like this:
SELECT ... WHERE `id` > 0 AND `id_parent` = 1 ORDER BY `id` DESC . It seems to me that this is a bit of a strange crutch and it would be easier to create 2 composite indexes: PK `id` + `id_parent` and the usual `id_parent` + `id`.
Please help me figure out how to do this...
P.S. Volumes of operations per day: 400-450 thousand INSERT, 400-450 thousand DELETE, and several tens of millions of SELECT. Of course, I delete 1 time per day when there is no activity on the site.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question