2

This is probably just me misunderstanding how MySQL partitions work, but I have a table defined with:

  `ID` int(11) NOT NULL,
  `target_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL,

  PRIMARY KEY (`ID`, `created_at`),
  KEY `index_created_at_target_id` (`created_at` desc, `target_id`)
  KEY `index_on_created_at` (`created_at`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY HASH(MONTH(`created_at`))
PARTITIONS 12

If I query the data with a single created_at timestamp:

select * from my_table where `created_at`= '2018-12-00 05:00:00' AND target_id in (6,7,8);

Then the explain knows to only hit 1 partition. This is correct.


However, if I query on a range:

select * from my_table where `created_at` BETWEEN '2018-12-00 05:00:00' AND '2019-01-04 04:59:59' AND target_id in (6,7,8);

The explain now hits all partitions. Is this a known limitation, or am I just doing something wrong?


Side note; judging by other "partitioning" questions, I expect a couple "partitioning doesn't help just index correctly" responses... I'm just trying it for myself and gathering some metrics so we can find the solution that performs best for our needs. (also partitioning the table, even when it then checks all partitions, still cut the query time in half versus having 1 monolithic table with the same indexes; 2.3s vs 0.8s)


Edit 1:

I did make sure that both queries do hit the index_on_created_at index. The only difference is a 'non unique key lookup' versus an 'index range scan' on the index.

  • You are ahead of most MySQL users who are thinking about Partitioning. I hope my Answer will accelerate your learning. – Rick James Dec 7 '18 at 22:55
1

You have just found one of many reasons why PARTITIONing is mostly useless.

Even with your first SELECT, there is no performance benefit over what you could have with a non-partitioned table with a suitable index. j In particular:

INDEX(target_id, created_at)  -- in this order

The second SELECT shows how PARTITION BY HASH treats a range -- by looking in all partitions. It has no choice but to do that. Yeah, yeah, with a lot more smarts, it could notice that that date range should hit only two months. Even so, non-partitioned with a suitable index would run faster and be simpler.

Other comments about the indexes:

KEY `index_created_at_target_id` (`created_at` desc, `target_id`)
KEY `index_on_created_at` (`created_at`)

has two issues:

  • INDEX(a,b), INDEX(a) -- you may as well toss the latter since the former can serve its purposes.
  • Until MySQL 8.0, DESC is ignored in INDEX definitions. (Since you don't have an ORDER BY that depends on it, there is no harm done.)

If your goal is to understand what partitioning can/cannot do, I recommend my blog .

even when it then checks all partitions, still cut the query time in half versus having 1 monolithic table with the same indexes

I dispute that. Did you run with the Query cache off? Did you run each test twice -- to avoid buffer_pool_ caching differences? Actually, the key is probably "with the same indexes". Almost always, when switching between partitioned and non-partitioned, the indexes need to be revised.

  • When partitioned, put the partition key later in each index where it is needed. Or leave it out if the partition pruning suffices.
  • When not partitioned, non-range columns should be early in indexes.
  • When not partitioned, range columns (eg date range) should be late in indexes.

Why even a point query does not speed up with partitioning. Notice how either way seems to be about the same amount of work:

  • Non partitioned: BTree index that is used is, say, 3 levels deep. Hop-skip-jump, there is the row you desire.
  • Partitioned: First prune to the desired partition ("hop"). The, say, the BTree is only 2 levels deep ("skip-jump").

Ah. Maybe you can discover a 5th use case for Partitioning.

  • Thanks for the reply. Part of the reason I was pursuing partitioning was because, lets say I have 17mil records that span a year. If I query for a date range of 1 month, mysql will index range scan an index (the created_by index specifically). As soon as I increase that range to 2 months (other wise same predicate) it starts doing a full index scan on a different index (not shown here, but that index doesn't contain created_at or target_id) seemingly over all 17m records, and query time jumps from like 2s to 28s. I had hoped that the partition would give it less index to scan :shrug: – CodingWithSpike Dec 8 '18 at 18:03
  • Re: your comments on the query times. I actually just took a table with 2.5mil rows, created a duplicate of the same table, indexes, data, but partitioned and am running the same query a couple times against each table. (i wasn't switching 1 table between partitioned and not; I made separate tables with the same data) – CodingWithSpike Dec 8 '18 at 18:06
  • One more thing I just noticed; when looking at the explain for the queries, the non-partitioned table query says it's index range scanning 691k rows. Against the partitioned table it says it is index range scanning 96k rows. I think that explains the drastic speed difference. The tables have the same row count and queries return the same result. Not sure how to account for that difference... – CodingWithSpike Dec 8 '18 at 19:11
  • @CodingWithSpike - (comment 1) BY HASH(MONTH()) is useless if you need more than one month. BY RANGE(TO_DAYS()) stands a chance of doing "partition pruning". Non-partitioned, it scans what is needed. (comment 3) I need to see the specifics in order to comment. – Rick James Dec 8 '18 at 20:00
  • @CodingWithSpike - If you provide (1) the CREATE TABLE, but without the keys and partitioning, and (2) examples of the 'important' queries, then I can take a holistic approach to see what index(es) and partitioning makes most sense for all of those queries. It's a Wakamole game -- focusing on one query is too likely to mess up some other query. – Rick James Dec 8 '18 at 20:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.