How to select from a range of partitions in MySQL-CodePudding

I hope you can help me!

I have a number of partitions in my database for the last few years. Their naming convention is pYYYYMM. For example p202206 would be for June 2022.

How would I go about saying: SELECT * FROM TABLE where partitions are within last 6 months?

The below works - does this seem more efficient than having date ranges in the where statement as you are explictly specifying the partitions to search?

    WITH t1 as (SELECT GROUP_CONCAT(PARTITION_NAME) FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'ran' AND TABLE_NAME = 'order_data' and PARTITION_DESCRIPTION > 2016)
SELECT * INTO @items FROM t1;
WITH t2 as (select concat('select * from order_data PARTITION(',@items,')'))
SELECT * INTO @query FROM t2;
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Thanks very much in advance!

CodePudding user response：

I would just use the date range in the WHERE clause.

You can confirm it will do partition pruning with EXPLAIN PARTITIONS (I think recent versions of MySQL include the partition info by default in EXPLAIN output).

If partition pruning works, it is practically the same in performance as specifying the partitions.

You should only specify partitions if your query conditions don't allow partition pruning to work automatically.