Comments on: Why SELECT COUNT(*) FROM TABLE Is Sometimes Very Slow in MySQL or MariaDB https://www.percona.com/blog/why-select-count-from-table-is-sometimes-very-slow-in-mysql-or-mariadb/ Thu, 18 Apr 2024 21:30:46 +0000 hourly 1 https://wordpress.org/?v=6.5.2 By: Jean-François Gagné https://www.percona.com/blog/why-select-count-from-table-is-sometimes-very-slow-in-mysql-or-mariadb/#comment-10974290 Thu, 18 Apr 2024 21:30:46 +0000 https://www.percona.com/blog/?p=95505#comment-10974290 Nice finding Przemysław. About MVCC, I think the variations are caused by a combination of the plan chosen by the optimizer (you imply this by mentioning Bug #80580 and WL #10398)) and the way InnoDB implements MVCC on secondary indexes (you do not mention this at all). I am giving more info about InnoDB MVCC and secondary indexes below.

My recollection is vague on the subject, not much time to find references, but from what I understood (probably from reading content fromn Mark Callaghan or Marko Mäkelä), secondary indexes do not contain “row level MVCC information”, only “page level MVCC information”. So when this page-level information tells InnoDB that something is “outdated” in the secondary index, a clustered-index lookup is needed to get the “good” version of the row. It looks like the slow-log entry showing a slower query matches this as the number of “Pages_accessed” matches the number of Rows_examined (11.510.893 and 10.000.000). So with DELETE, if the optimizer uses the clustered-index, things will be fast (might scan more data), but if using a secondary index, the best case is better (scanning less data) and the worse case is much worse (needing a PK lookup for each row).

I actually found a reference to this in Marko’s talk at the MariaDB 2023 (Un)Conference: How InnoDB Undo Logs and the Purge of History Work (goto 5:38 in YouTube video).

]]>