MySQL Forums
Forum List  »  Partitioning

(non-unique) index more efficient than partition pruning
Posted by: Miko M
Date: November 19, 2014 12:27AM

In this case using (non-unique) index seems to be far more efficient than partition pruning.
Actually it is unpleasant surprise that no index is used together with partition pruning, even with 'use index' clause.

MariaDB [qns]> select count(*) from Table1 where col2 like concat(substr(TIMESTAMPADD(MINUTE,-10,NOW()),1,15),'%');
+----------+
| count(*) |
+----------+
| 5210352 |
+----------+
1 row in set (4.88 sec)

MariaDB [qns]> select count(*) from Table1 where col20 between now() - interval 15 minute and now() + interval 5 minute and col2 like concat(substr(TIMESTAMPADD(MINUTE,-10,NOW()),1,15),'%');
+----------+
| count(*) |
+----------+
| 5210352 |
+----------+
1 row in set (33.25 sec)

MariaDB [qns]>
MariaDB [qns]> explain select count(*) from Table1 where col2 like concat(substr(TIMESTAMPADD(MINUTE,-10,NOW()),1,15),'%');
+------+-------------+---------------------+-------+---------------+-----------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------------------+-------+---------------+-----------+---------+------+------+--------------------------+
| 1 | SIMPLE | Table1 | range | idx2 | idx2 | 26 | NULL | 10 | Using where; Using index |
+------+-------------+---------------------+-------+---------------+-----------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

MariaDB [qns]> explain partitions select count(*) from Table1 where col2 like concat(substr(TIMESTAMPADD(MINUTE,-10,NOW()),1,15),'%');
+------+-------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+-----------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+-----------+---------+------+------+--------------------------+
| 1 | SIMPLE | Table1 | P1970010101,P2014111904,P2014111905,P2014111906,P2014111907,P2014111908,P2014111909,P2014111910,P2014111911,P2014111912,P2014111913,P2014111914,P2014111915,P2014111916,P2014111917,P2014111918,P2014111919,P2014111920,P2014111921,P2014111922,P2014111923,P2014112000,P2014112001,P2014112002,P2014112003,P2014112004 | range | idx2 | idx2 | 26 | NULL | 10 | Using where; Using index |
+------+-------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+-----------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

MariaDB [qns]>
MariaDB [qns]>
MariaDB [qns]> explain select count(*) from Table1 where col20 between now() - interval 15 minute and now() + interval 5 minute and col2 like concat(substr(TIMESTAMPADD(MINUTE,-10,NOW()),1,15),'%');
+------+-------------+---------------------+------+---------------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------------------+------+---------------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | Table1 | ALL | idx0,idx2 | NULL | NULL | NULL | 23460109 | Using where |
+------+-------------+---------------------+------+---------------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)

MariaDB [qns]>
MariaDB [qns]> explain partitions select count(*) from Table1 where col20 between now() - interval 15 minute and now() + interval 5 minute and col2 like concat(substr(TIMESTAMPADD(MINUTE,-10,NOW()),1,15),'%');
+------+-------------+---------------------+-------------+------+---------------------+------+---------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------------------+-------------+------+---------------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | Table1 | P2014111916 | ALL | idx0,idx2 | NULL | NULL | NULL | 23676827 | Using where |
+------+-------------+---------------------+-------------+------+---------------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)



All in all, it also seems that this statement is true: "Avoid Index = Partitioning column"
idx0 -> col20 is the partitioning key does not help here.

Options: ReplyQuote


Subject
Views
Written By
Posted
(non-unique) index more efficient than partition pruning
1252
November 19, 2014 12:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.