(non-unique) index more efficient than partition pruning

[Miko M]

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.