CODEWITHSUNDEEP
mysqlpartitioningdatabase-optimization
Gaurang
Gaurang

Reputation: 371

Fetch data from multiple table partitions using a single query in MYSQL

I have created a table in MYSQL using following syntax:

CREATE TABLE  `demo` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `date` datetime NOT NULL COMMENT 'date',
  `desc` enum('error','audit','info') NOT NULL,
  PRIMARY KEY (`id`,`date`)
)
PARTITION BY RANGE (MONTH(`date`))
(
 PARTITION JAN VALUES LESS THAN (2),
 PARTITION FEB VALUES LESS THAN (3),
 PARTITION MAR VALUES LESS THAN (4),
 PARTITION APR VALUES LESS THAN (5),
 PARTITION MAY VALUES LESS THAN (6),
 PARTITION JUN VALUES LESS THAN (7),
 PARTITION JUL VALUES LESS THAN (8),
 PARTITION AUG VALUES LESS THAN (9),
 PARTITION SEP VALUES LESS THAN (10),
 PARTITION OCT VALUES LESS THAN (11),
 PARTITION NOV VALUES LESS THAN (12),
 PARTITION `DEC` VALUES LESS THAN (MAXVALUE)
);

Here id and date is the combined primary key and I have used date as the partitioning column. I am making the partitions based on month in the date. The table is created successfully and the data is getting inserted properly into it as per the partitions.

What will be the effect on the performance if I fire a query which needs to fetch records across multiple partitions?

Consider following query:

SELECT * FROM `demo` WHERE `between` '2015-02-01 00:00:00' AND '2015-05-31 00:00:00';

Upvotes: 1

Views: 1980

Answers (1)

Rick James
Rick James

Reputation: 142356

The query will need to look at ALL the partitions. The optimizer is not smart enough to understand the basic principles of date ranges when they are "wrapped" by the MONTH() function.

You can see this by doing EXPLAIN PARTITIONS SELECT ...;.

Even if it were smart enough to touch only 4 partitions, you would gain no performance benefit for that SELECT. You may as well get rid of partitions and add an index on date.

Since this table is called demo, I suspect it is not the final version. If you would like to talk about whether PARTITIONing is useful for your application, let's see the real schema and the important queries.

Upvotes: 1

Related Questions