Covering Index Not Being Used Effectively

Question

I have a unique composite index on three fields (hash, is_customer, user_id) in a table a.

And queries that go like this:

select `a`.`id`, `hash`, `is_customer`, `user_id` 
from `a` 
where `hash` = 34605915 
limit 1;

When I do explain, it identifies properly as:

Using where; Using index |

However the above query showed up in a slow query log as Full_scan: Yes and query time of over 5.083821 seconds.

The dump of the explain is as follows:

| {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "a",
      "access_type": "index",
      "possible_keys": ["a_hash_unique"],
      "key": "a_id_hash_is_customer_user_id_unique",
      "key_length": "138",
      "used_key_parts": ["id", "hash", "is_customer", "user_id"],
      "rows": 2639299,
      "filtered": 100,
      "attached_condition": "db.a.hash = 34605915",
      "using_index": true
    }
  }
} |

The InnoDB pool buffer pool size is set to 21G (actual pool data size is about 18.1G) and pool instances as 21. What could be the explanation for the optimized missing this query index? In the process, I think the same issue causes the system to run into deadlocks during DB writes.

Below is the table structure dump:

 a | CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `anum` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `currency` char(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'NGN',
  `hash` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `bid` int(10) unsigned DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `a_type` int(10) unsigned DEFAULT NULL COMMENT '1=Current, 2=Savings, 3=Fixed, 4=Overdraft, 5=Loan, 6=Joint, 7=Other',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `is_customer` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a_hash_unique` (`hash`),
  UNIQUE KEY `a_id_hash_is_customer_user_id_unique` (`id`,`hash`,`is_customer`,`user_id`),
  KEY `a_bid_index` (`bid`),
  KEY `a_user_id_index` (`user_id`),
  CONSTRAINT `a_bid_foreign` FOREIGN KEY (`bid`) REFERENCES `b` (`id`),
  CONSTRAINT `a_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6004761 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

Answer 1

Using index means that an index was "covering" and was used. Or, at least, when the EXPLAIN was run, it decided that.

I have looked at thousands of EXPLAINs and slowlog entries. In theory, they always match. However, there is nothing that forces the EXPLAIN to actually match what happens and is recorded in the slowlog.

I have seen as many as 6 different EXPLAINs for a given query "digest". But that is easily explained by different cardinality, etc.

Could you provide the full, exact, text of the EXPLAIN and the slowlog entry. I would like to see if I can spot something. Also, if available, please provide EXPLAIN FORMAT=JSON SELECT ...

Actually, SHOW CREATE TABLE would probably have the clue.

WHERE `hash` = 34605915

If hash is VARCHAR, then this cannot use the index. Add quotes to the string "34605915".

Or, if hash is always numeric, then make it an integer datatype, perhaps INT UNSIGNED (32-bit number; 4-bytes).

When mixing char and int, MySQL will convert the varchar to int before comparing. So, the one case of "varchar_col = integer-constant" cannot use the index effectively.