Search code examples
mysqlmariadbdatabase-partitioningtable-partitioning

Does MySQL Partitioning Require Special Queries To Take Advantage After Creating It?

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?

Solution

  • 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.