1

I'm a newbie when it comes to MySQL/MariaDB partitions, and haven't created one yet, but am reading up on it. My first question is, if I partition a table by year and then month based on a dt_created DATETIME column, do I need to change the way I'm doing SQL queries in order to start to see a performance increase when I'm doing a single day query on dt_created? Or, does a standard query such as:

SELECT * FROM web_tracking_events where dt_created >= '(some time goes here)'

work good enough?

Volomike
  • 23,743
  • 21
  • 113
  • 209
  • 1
    See http://mysql.rjweb.org/doc.php/partitionmaint, if you haven't already – Strawberry Aug 22 '20 at 06:04
  • 1
    Partitioning does not inherently produce any performance benefit. (There are a small number of exceptions; your use case is not one of them.) As for the Title question -- no syntax changes in the queries. – Rick James Aug 23 '20 at 14:14
  • @RickJames I understand that you're an accepted pro at this. So, you talked me out of partitioning. Thanks for your rich information about this on the web! :) – Volomike Sep 12 '20 at 04:14

1 Answers1

1

Basically. you can do a query like:

SELECT * FROM web_tracking_events where dt_created >= '(some time goes here)'

This is called pruning. See https://dev.mysql.com/doc/refman/8.0/en/partitioning-pruning.html

However, that means that mysql will open all partitions to check if it finds a match there.

Volomike
  • 23,743
  • 21
  • 113
  • 209
nbk
  • 45,398
  • 8
  • 30
  • 47