MySQL Forums :: Partitioning :: large InnoDB table partitioning without explicit PK


Advanced Search

(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
large InnoDB table partitioning without explicit PK 2492 Miko M 11/12/2014 07:41PM
Re: large InnoDB table partitioning without explicit PK 1410 Rick James 11/13/2014 06:40PM
Re: large InnoDB table partitioning without explicit PK 1243 Miko M 11/14/2014 03:02AM
Re: large InnoDB table partitioning without explicit PK 989 Rick James 11/15/2014 12:05AM
Re: large InnoDB table partitioning without explicit PK 1011 Miko M 11/16/2014 07:39PM
Re: large InnoDB table partitioning without explicit PK 893 Rick James 11/17/2014 04:21PM
Re: large InnoDB table partitioning without explicit PK 996 Rick James 11/17/2014 07:48PM
Re: large InnoDB table partitioning without explicit PK 962 Miko M 11/18/2014 02:00AM
Re: large InnoDB table partitioning without explicit PK 1005 Rick James 11/18/2014 11:35PM
Re: large InnoDB table partitioning without explicit PK 1053 Miko M 11/22/2014 06:44AM
Re: large InnoDB table partitioning without explicit PK 946 Miko M 11/18/2014 01:13AM
(non-unique) index more efficient than partition pruning 1173 Miko M 11/19/2014 12:27AM
Re: (non-unique) index more efficient than partition pruning 1044 Rick James 11/19/2014 06:01PM
Re: (non-unique) index more efficient than partition pruning 1073 Miko M 11/22/2014 08:39AM
Re: large InnoDB table partitioning without explicit PK 1060 Miko M 11/14/2014 03:04AM
Re: large InnoDB table partitioning without explicit PK 1111 Miko M 11/14/2014 03:05AM
Re: large InnoDB table partitioning without explicit PK 1203 Rick James 11/15/2014 12:32AM
Re: large InnoDB table partitioning without explicit PK 1129 Miko M 11/16/2014 08:31PM
Re: large InnoDB table partitioning without explicit PK 966 Rick James 11/19/2014 12:12AM
Re: large InnoDB table partitioning without explicit PK 938 Miko M 11/22/2014 09:07AM
Re: large InnoDB table partitioning without explicit PK 925 Rick James 11/23/2014 09:00PM
Re: large InnoDB table partitioning without explicit PK 1015 Miko M 12/01/2014 11:16AM


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.