MySQL join is scanning the table rather than using the index

user_2603238 0
Viewed 46

I have following tables:

SHOW CREATE TABLE access_token_status;

CREATE TABLE `access_token_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` varchar(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `status` (`status`),
  KEY `idx_access_token_status_status_lookup_2` (`status`,`id`),
  KEY `idx_access_token_status_status_lookup_1` (`id`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

and

SHOW CREATE TABLE user;

CREATE TABLE `user` (
  `id` varchar(17) NOT NULL,
  `short_lived_access_token_status_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_status_lookup_1` (`id`,`short_lived_access_token_status_id`),
  KEY `idx_user_status_lookup_2` (`short_lived_access_token_status_id`,`id`),
  KEY `ix_user_short_lived_access_token_status_id` (`short_lived_access_token_status_id`),
  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`short_lived_access_token_status_id`) REFERENCES `access_token_status` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

and

SHOW CREATE TABLE account;

CREATE TABLE `account` (
  `id` varchar(17) NOT NULL,
  `user_id` varchar(17) NOT NULL,
  `track` tinyint(1) NOT NULL,
  `estimated_time_to_regain_access` int(11) NOT NULL,
  `media_list_fetched_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_account_next_fetch_lookup` (`user_id`,`estimated_time_to_regain_access`,`track`,`media_list_fetched_at`),
  CONSTRAINT `account_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
  CONSTRAINT `account_chk_1` CHECK ((`track` in (0,1)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

When I try to explain the following query

explain select a.*
        from account a
        inner join user u
        on a.user_id = u.id
        inner  join access_token_status as s
        on u.short_lived_access_token_status_id = s.id and s.status = 'valid'
        where
            u.short_lived_access_token_status_id = 3
            and a.estimated_time_to_regain_access = 0
            and a.track = true
            and a.media_list_fetched_at > '2020-05-30 12:31:01'
        limit 1
        for update of a SKIP LOCKED

I get this output:

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 's', NULL, 'const', 'PRIMARY,status,idx_access_token_status_status_lookup_2,idx_access_token_status_status_lookup_1', 'PRIMARY', '4', 'const', '1', '100.00', NULL
'1', 'SIMPLE', 'a', NULL, 'index', 'idx_account_next_fetch_lookup', 'idx_account_next_fetch_lookup', '80', NULL, '2', '50.00', 'Using where; Using index'
'1', 'SIMPLE', 'u', NULL, 'eq_ref', 'PRIMARY,idx_user_status_lookup_1,idx_user_status_lookup_2,ix_user_short_lived_access_token_status_id', 'PRIMARY', '70', 'media_meta.a.user_id', '1', '100.00', 'Using where'

It seems like indexes are not being used for some tables. This is an issue for my case as the scanned rows get locked and other queries will skip them (due to SKIP LOCKED which is required to make sure queries are not blocked on each other)

I'm not sure what index I'm missing or if I need to change something in the query

join sql-execution-plan query-optimization mysql
2 Answers

In your query you use "u.short_lived_access_token_status_id = 3", so the join with "s" looks either impossible (if s.status for s.id = 3 is NOT "valid") or superfluous (if it is). Unless you are fetching some other columns from s.

Let us now see where "u" is used:

on a.user_id = u.id
...
    on u.short_lived_access_token_status_id = ...

So you are using a main selection criterion based on u.short_lived_access_token_status_id = 3, and from this you need to get u.id. So your idx_user_status_lookup_2 ought to work as a covering index.

Why doesn't it? Possibly because the table is so small that it doesn't matter very much, or because the join with S is interfering with the optimizer (you'll note that the table u is evaluated third).

If at all possible, try removing the join with s and see what happens.

user_14286790

Seems to be "over-normalization".

I don't see any advantage in moving status out of the table into another table. Doing so complicates optimization and does not save much, if any, space.

In fact, you could use an ENUM('invalid', 'valid', ...) NOT NULL, which would take 1 byte instead of 4 bytes for INT.

In InnoDB, when you have PRIMARY KEY(id), you have a BTree ordered by id. Hence, any secondary index starting with id is redundant. Note also that a PRIMARY KEY is, by definition, UNIQUE.

    where
        u.short_lived_access_token_status_id = 3
        and a.estimated_time_to_regain_access = 0
        and a.track = true
        and a.media_list_fetched_at > '2020-05-30 12:31:01'

would like to use either

INDEX(short_lived_access_token_status_id, ...)

but that seems very unlikely due to low cardinality, or

INDEX(estimated_time_to_regain_access, track,   -- in either order
      media_list_fetched_at)    -- last, since it is a 'range'

Even better would be this "covering" index:

INDEX(estimated_time_to_regain_access, track,
      media_list_fetched_at, user_id)
user_17668310
Related