0

I'm trying to partitionate a table that has almost 1TB in size in MySQL, I already dropped al my FK using this command for all the FK:

ALTER TABLE mytable DROP FOREIGN KEY my_fk;

but when I try to create my partitions like this:

ALTER TABLE records
PARTITION BY RANGE( YEAR(`when`) ) (
    PARTITION d0 VALUES LESS THAN (2020),
    PARTITION d1 VALUES LESS THAN (2021),
    PARTITION d2 VALUES LESS THAN (2022),
    PARTITION d3 VALUES LESS THAN (2023),
    PARTITION d4 VALUES LESS THAN (2024),
    PARTITION d5 VALUES LESS THAN (2025),
    PARTITION d6 VALUES LESS THAN (2026),
    PARTITION d7 VALUES LESS THAN MAXVALUE
);

I get this error: ERROR 1506 (HY000): Foreign keys are not yet supported in conjunction with partitioning

and when I describe my table, I can see that those fields are marked as FK:

+--------------------+-------------+------+-----+---------+----------------+
| Field              | Type        | Null | Key | Default | Extra          |
+--------------------+-------------+------+-----+---------+----------------+
| id                 | int(11)     | NO   | PRI | NULL    | auto_increment |
| deleted            | tinyint(1)  | NO   |     | NULL    |                |
| uuid               | char(32)    | NO   | UNI | NULL    |                |
| when               | datetime(6) | NO   | MUL | NULL    |                |
| status             | varchar(4)  | NO   |     | NULL    |                |
| location_id        | int(11)     | YES  | MUL | NULL    |                |
| user_id            | int(11)     | NO   | MUL | NULL    |                |
| series_id          | int(11)     | YES  | MUL | NULL    |                |
+--------------------+-------------+------+-----+---------+----------------+

Am I wrongly deleting the FK relations, or do I need to do something like refreshing the Schema? I already stopped and restarted the docker container where the DB lives, but nothing happened.

Thanks in advance.

6
  • 1
    What do you hope to gain by partitioning? It won't help with SELECTs. It will help with DELETEs, assuming you switch to DROP PARTITION.
    – Rick James
    May 16, 2023 at 20:09
  • I want my queries to be a little faster. I'm joining my table (the one that I want to partitionate) with other tables. I assume that partitioning a small table with another, would help a lot on my queries. EDIT: I know that removing the FK wont help at joining tables. That's why I'm also investigating in migrate to Postgresql. I can partitionate there without losing FKs. May 22, 2023 at 17:35
  • No, partitioning does not help with most SELECTs. If you provide SELECTs, we can discuss in more detail.
    – Rick James
    May 22, 2023 at 18:32
  • But what about partition pruning? Some articles say that it can boost queries in order of magnitudes sometimes. We built a system on top of Django's ORMs that let users create their own query, so they usually join other tables (or not) but almost all the queries have a "when" field, that is where the row was created on the DB, and we set that field to two weeks by default. That fieeld is already and index on our DB, and our queries are getting slower and slower because our tables are getting really big over time. May 22, 2023 at 18:57
  • Usually an index does as good or better than pruning. Your example sounds like an inadequate single-column index. Would you care to share the query and the index.
    – Rick James
    May 22, 2023 at 19:27

1 Answer 1

0
SELECT  DISTINCT records.* user.*, serie.* location.*
    FROM  records
    INNER JOIN  user  ON records.user_id = user.id
    LEFT OUTER JOIN  serie  ON records.serie_id = serie.id
    LEFT OUTER JOIN  location  ON records.location_id = location.id
    WHERE  records.deleted = 0
      AND  records.when > '2023-03-22 12:50:37.034779'
    ORDER BY  records.id DESC
    LIMIT  20;

Add INDEX(deleted, when, id) and change to ORDER BY records.when DESC, id DESC. This, alone, may eliminate any benefit from PARTITIONing.

Is there exactly one "user" per record? Is there at most one series and location per record? If so, get rid if DISTINCT. After understanding the relationships, I may have a further rewrite for the LEFTs.

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.