Re: Count(*) query is slower on partitioned table

[Darren Demicoli]

Thanks for your reply.

In my real case the partitioning is on a datetime column - the actual idea is to use partitions to simplify deletions and reclamation of storage space. So, we are not after a performance improvement, but such a degradation is very worrying.

In the meantime, another thing I have noted is that as more columns are added (alter) to the partitioned table, then this COUNT query takes longer and longer. (this may be one of the reason why my actual query takes much longer). Once again, this does not make sense to me because this query should be "using index" - ie: should not need to access the main table, right? So my "suspicion" is that something is wrong in the Mysql/mariadb part that handles partitioning such that InnoDb still has to reference the main table. I will try to test with most recent version of Mysql 5.7 to ensure it is not related to index condition pushdown (even though I do not have a filtering condition here) and get back to you.

--
Darren Demicoli
Devops Engineer
http://www.xcaliber.com