Mysql – Strange behaviour with MySQL 5.1 TO_DAYS partitioning schema

MySQLpartitioning

I have this table definition:

CREATE TABLE `va_ventas` (
`ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`Date` DATE NOT NULL,
`Time` TIME NULL DEFAULT NULL,
`Country` VARCHAR(3) NULL DEFAULT NULL,
`Type` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`Result` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
`Carrier` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
`WM_Alias` VARCHAR(75) NOT NULL,
`Product_Code` VARCHAR(75) NOT NULL,
`Platform_Code` VARCHAR(40) NOT NULL,
`AltaID` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
`ClienteID` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
`Commission_Code` VARCHAR(40) NULL DEFAULT NULL,
INDEX `Date_WM_Alias_Product_Code` (`Date`, `WM_Alias`, `Product_Code`),
INDEX `Date_WM_Alias` (`Date`, `WM_Alias`),
INDEX `Date_Carrier_WM_Alias` (`Date`, `Carrier`, `WM_Alias`),
INDEX `AltaID` (`AltaID`),
INDEX `ClienteID` (`ClienteID`),
INDEX `Country` (`Country`),
PRIMARY KEY (`ID`, `Date`)
)
COLLATE='utf8_general_ci' ENGINE=MyISAM;

With the next partition schema:

PARTITION BY RANGE(TO_DAYS(Date)) 
(PARTITION p_2012_01 VALUES LESS THAN (TO_DAYS('2012-01-01')),
PARTITION p_2012_02 VALUES LESS THAN (TO_DAYS('2012-02-01')),
PARTITION p_2012_03 VALUES LESS THAN (TO_DAYS('2012-03-01')),
PARTITION p_2012_04 VALUES LESS THAN (TO_DAYS('2012-04-01')),
PARTITION p_2012_05 VALUES LESS THAN (TO_DAYS('2012-05-01')),
PARTITION p_2012_06 VALUES LESS THAN (TO_DAYS('2012-06-01')),
PARTITION p_2012_07 VALUES LESS THAN (TO_DAYS('2012-07-01')),
PARTITION p_2012_08 VALUES LESS THAN (TO_DAYS('2012-08-01')),
PARTITION p_2012_09 VALUES LESS THAN (TO_DAYS('2012-09-01')),
PARTITION p_2012_10 VALUES LESS THAN (TO_DAYS('2012-10-01')),
PARTITION p_2012_11 VALUES LESS THAN (TO_DAYS('2012-11-01')),
PARTITION p_2012_12 VALUES LESS THAN (TO_DAYS('2012-12-01')),
PARTITION p_2013_01 VALUES LESS THAN (TO_DAYS('2013-01-01'))
);

I filled the table with 10.000 dummy data entries and when I use EXPLAIN in a sample query I get the next result:

mysql> EXPLAIN PARTITIONS SELECT COUNT(*) FROM va_ventas WHERE Date BETWEEN '2012-08-01' AND '2012-10-20'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: va_ventas
   partitions: p_2012_01,p_2012_09,p_2012_10,p_2012_11
         type: index
possible_keys: Date_WM_Alias_Product_Code,Date_WM_Alias,Date_Carrier_WM_Alias
          key: PRIMARY
      key_len: 7
          ref: NULL
         rows: 2591
        Extra: Using where; Using index

I don't know why it's using the p_2012_01 partition where it's not between the date specified in the BETWEEN statement. Also, shouldn't this select the partition p_2012_08 to match the statement? The data in the table is well distributed, so each date gets a similar amount of entries.

Thanks in advance.

EDIT

More information, I changed my partition schema to the next one, that makes more sense:

PARTITION BY RANGE(TO_DAYS(Date)) 
(PARTITION pNULL VALUES LESS THAN (0),
PARTITION p201201 VALUES LESS THAN (TO_DAYS('2012-02-01')),
PARTITION p201202 VALUES LESS THAN (TO_DAYS('2012-03-01')),
PARTITION p201203 VALUES LESS THAN (TO_DAYS('2012-04-01')),
PARTITION p201204 VALUES LESS THAN (TO_DAYS('2012-05-01')),
PARTITION p201205 VALUES LESS THAN (TO_DAYS('2012-06-01')),
PARTITION p201206 VALUES LESS THAN (TO_DAYS('2012-07-01')),
PARTITION p201207 VALUES LESS THAN (TO_DAYS('2012-08-01')),
PARTITION p201208 VALUES LESS THAN (TO_DAYS('2012-09-01')),
PARTITION p201209 VALUES LESS THAN (TO_DAYS('2012-10-01')),
PARTITION p201210 VALUES LESS THAN (TO_DAYS('2012-11-01')),
PARTITION p201211 VALUES LESS THAN (TO_DAYS('2012-12-01')),
PARTITION p201212 VALUES LESS THAN (TO_DAYS('2013-01-01')),
PARTITION p201301 VALUES LESS THAN (TO_DAYS('2013-02-01')),
PARTITION pMAX VALUES LESS THAN MAXVALUE
);

After filling, this is the status of the table:

SELECT PARTITION_NAME,TABLE_ROWS FROM 
INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'va_ventas';

+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| pNULL          |          0 |
| p201201        |        846 |
| p201202        |        793 |
| p201203        |        847 |
| p201204        |        820 |
| p201205        |        847 |
| p201206        |        819 |
| p201207        |        847 |
| p201208        |        847 |
| p201209        |        820 |
| p201210        |        847 |
| p201211        |        820 |
| p201212        |        847 |
| p201301        |          0 |
| pMAX           |          0 |
+----------------+------------+
15 rows in set (0.11 sec)


EXPLAIN PARTITIONS SELECT SQL_NO_CACHE 
COUNT(*) FROM va_ventas WHERE Date >= '2012-05-01' AND Date IS NOT NULL\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: va_ventas
   partitions: pNULL,p201205,p201206,p201207,p201208,p201209,p201210,p201211,p201212,p201301,pMAX
         type: index
possible_keys: PRIMARY,Date_WM_Alias
          key: PRIMARY
      key_len: 7
          ref: NULL
         rows: 6694
        Extra: Using where; Using index
1 row in set (0.00 sec)

It's still selecting the pNULL partition althought it's clearly that have 0 rows in it. Also, selection a date directly does not select the pNULL partition:

EXPLAIN PARTITIONS SELECT SQL_NO_CACHE COUNT(*) FROM va_ventas WHERE Date = '2012-05-01' AND Date IS NOT NULL\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: va_ventas
   partitions: p201205

Any thoughts?

Best Answer

sorry, the comment of @phil aroused my curiosity so I decided to cheat and to read the manual:

18.2.7. How MySQL Partitioning Handles NULL

Partitioning in MySQL does nothing to disallow NULL as the value of a partitioning expression, whether it is a column value or the value of a user-supplied expression. Even though it is permitted to use NULL as the value of an expression that must otherwise yield an integer, it is important to keep in mind that NULL is not a number. MySQL's partitioning implementation treats NULL as being less than any non-NULL value, just as ORDER BY does.

Related Solutions

MySQL looking up more rows than needed (indexing issue)

Your indexes are fine for the two types of queries you mentioned.

This query will be satisfied by traversing the clustered index on the primary key...

[...] WHERE participant_id = x AND question_id = y AND given_answer_id = z;

...and this one is satisfied by the index on 'question_id':

[...] WHERE question_id = x;

The output of EXPLAIN SELECT is not telling you what you think it is telling you, because the value shown in rows is an estimate of the number of rows the server will need to consider, not the actual rows it will examine. For InnoDB these are based on index statistics.

rows

The rows column indicates the number of rows MySQL believes it must examine to execute the query.

For InnoDB tables, this number is an estimate, and may not always be exact.

^{— http://dev.mysql.com/doc/refman/5.5/en/explain-output.html#explain_rows}

The optimizer gathers information about different possible query plans, and chooses the one with the lowest cost. The information shown in EXPLAIN is the information the optimizer gathered about the plan it selected.

When type is ref and key is not NULL, this means that the name listed in the key column is the name of the index that the optimizer has chosen to use to find the desired rows, so your query plan looks exactly as it should.

Note, sometimes you will see Using index in the Extra column and a lot of people assume that this means an index is being used, or that no index is being used when that doesn't appear, but that's not correct, either. Using index describes a special case called a "covering index" -- it does not indicate whether an index is being used to locate the rows of interest.

It's possible that running ANALYZE [LOCAL] TABLE would cause the numbers in rows shown by EXPLAIN to differ, but this is a simple query and selecting this index is an obvious choice for the optimizer to make, so ANALYZE TABLE is unlikely to make any actual difference in performance.

It is possible, however, that your overall performance might see some marginal improvement with an occasional OPTIMIZE [LOCAL] TABLE, because you are not inserting rows in primary key order (as would be the case with an auto_increment primary key)... but on large tables this can be time-consuming because it rebuilds a new copy of the table... but, again, I wouldn't expect any significant change.

Mysql – Identical query, tables, but different EXPLAIN and performance

Just make a simple join. Sub-queries does not provide the best result quite often

EXPLAIN SELECT l.id, l.level_name, l.date_published, l.rating
FROM levels AS l
INNER JOIN users_favorites AS uf 
ON uf.level_id = l.id
WHERE l.user_id = 2;

Best Answer

Related Solutions

MySQL looking up more rows than needed (indexing issue)

Mysql – Identical query, tables, but different EXPLAIN and performance

Related Question