[MDEV-10128] Port MySQL 5.7 select count() optimization Created: 2016-05-26  Updated: 2019-01-09  Resolved: 2017-04-27

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Storage Engine - InnoDB, Storage Engine - XtraDB
Affects Version/s: 10.2.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Jan Lindström (Inactive) Assignee: Marko Mäkelä
Resolution: Won't Fix Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-11751 Merge new release of InnoDB MySQL 5.7... Closed
relates to MDEV-18188 Maintain persistent COUNT(*) in InnoDB Stalled

 Description   

Current handler::records() is not fully usable as InnoDB could roll back the statement on some cases and you may not continue after that, it should be int records(ha_rows *n_rows) and server should check as these errors will abort the current query:
case HA_ERR_LOCK_DEADLOCK:
case HA_ERR_LOCK_TABLE_FULL:
case HA_ERR_LOCK_WAIT_TIMEOUT:
case HA_ERR_QUERY_INTERRUPTED:

see commit 40ec5373c0 on 5.7

 Comments   
Comment by Marko Mäkelä [ 2017-04-26 ]

Oracle removed this in MySQL 5.7.18, because it is hard-wired to use the clustered index.
Sometimes using a secondary index could be a bad idea, because we do not have a hidden DB_TRX_ID column in the secondary indexes. For delete-marked records or for secondary index pages where PAGE_MAX_TRX_ID is too new, a lookup for a matching clustered index record must be performed (random lookups to the clustered index and optionally to undo log records). This is why it was hard-wired to use the clustered index in the first place.

See also my old blog post that mentions InnoDB secondary indexes.

Comment by Marko Mäkelä [ 2017-04-26 ]

MDEV-11751 (Merge MySQL 5.7.18 to MariaDB 10.2) is removing the count optimization.

Generated at Thu Feb 08 07:39:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.