0

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 |
2
  • Show full table's DDL. Add ORDER BY - LIMIT without it makes no sense. Tell how many rows there is in a table totally and what percent of them is selected without LIMIT. – Akina Aug 31 '20 at 17:13
  • I think ""access_type": "index"" also says that the index is "covering". WAIT! What's id doing at the start of the index?? Please provide SHOW CREATE TABLE. – Rick James Aug 31 '20 at 19:00
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.

4
  • Wow, hash is indeed a varchar, does it mean I need to quote hash as string in the query to use the index? – Chibueze Opata Aug 31 '20 at 18:55
  • @ChibuezeOpata - Yes, else it is obligated to convert each varchar to a number before testing. Alternatively, if the has is really just a number, change it to INT UNSIGNED or some other suitable integer datatype. Then the index will be very efficiently used. – Rick James Aug 31 '20 at 18:57
  • OK but even so, does this mean there will be a full table scan? – Chibueze Opata Aug 31 '20 at 19:01
  • @ChibuezeOpata - No. It will go directly to the desired row(s). – Rick James Sep 1 '20 at 4:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.