Cant perform partitioning after Foreign Keys were dropped

Question

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.

Answer 1

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.