0

I'm working in a system that is using MySQL as primary DB. One of the biggest tables is almost 1TB in size, others are around 100-300 GBs.

Queries started to be really slow.

I search some info, and I was planning to use partitioning by ID or by the created timestamp, but I dont really know what or how to do it.

I was thinking something like this (this is my local environment)

ALTER TABLE records
PARTITION BY RANGE (id) (
  PARTITION p0 VALUES LESS THAN (100),
  PARTITION p1 VALUES LESS THAN (200),
  PARTITION p2 VALUES LESS THAN (300),
  PARTITION p3 VALUES LESS THAN MAXVALUE
);

but I get Foreign keys are not yet supported in conjunction with partitioning.

So, I dont really know what to do now. I dont know if droping the FK is a good idea, specially for this kind of systems. Any help?

Also, I thought of migrating to something like PostGres or other engine that could help me of maintaining partitioning easily, but we really need to use Django ORM to query in our system. (Because all aplication is build on top of the ORM).

Thanks for reading. Any help would be appreciated.

2
  • 2
    This is not about MySQL but it answers your question nevertheless.
    – mustaccio
    Commented May 15, 2023 at 20:23
  • 2
    Asking about query tuning you must provide compete CREATE TABLE script and complete text of the query (or queries) to be improved.
    – Akina
    Commented May 16, 2023 at 5:02

1 Answer 1

0

Re MySQL...

Partition will not help with SELECTs. Especially not by the PRIMARY KEY. Partition by timestamp is useful only if you will be deleting "old" data.

FOREIGN KEYS add some burden to INSERTs. Maybe you should get rid of them?

Queries started to be really slow.

Let's see a really slow query or two. Please also provide SHOW CREATE TABLE.

migrating to ...

The main cost is disk access. Any decent RDBMS will need the same number of disk hits to achieve the apps goals.

For WHERE userid=123 AND date BETWEEN ... AND ..., The 'composite' INDEX(userid, date) does at least as well as partitioning and much better than the single column INDEX(date).

12
  • Partition will not help with SELECTs. ?? Partition Pruning may improve SELECT queries, sometimes dramatically.
    – Akina
    Commented May 16, 2023 at 5:05
  • 1
    @Akina So will a good index and no partitioning, by pretty much the same effect (unless skip-scanning is relevant) Commented May 16, 2023 at 10:37
  • 1
    So why is partitioning better than indexing? Partitioninng requires higher compilation times, a single index seek on a B+tree is going to be fast Commented May 16, 2023 at 10:58
  • 1
    @Charlieface - I argue against Partitioning in my blog: Partition. Part of my argument is to point out how few use cases actually benefit from partitioning.
    – Rick James
    Commented May 16, 2023 at 16:42
  • 1
    @DaniloBassi "queries that need perform a Join with other table, in that case, partitioning is better, I assume" - Wrong. Partitioning is a tool for data management not for improving SELECT performance, as everyone else here already mentioned. Indexes already partition the data in a way that's much more efficient than the linear distribution of partitioning. If your queries are slowing down, it's likely because they're poorly written and / or have incorrectly defined indexes. You should be asking questions specific to those slow queries on how to improve them, if you want performance help.
    – J.D.
    Commented May 16, 2023 at 19:12

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.