Does not use a primary key index

MySQL & MariaDB
1 
CREATE TABLE `users` (
  `user_id` int UNSIGNED NOT NULL,
  `email` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 PACK_KEYS=0;

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `users_unique_username` (`username`);

CREATE TABLE `user_data` (
  `user_id` int UNSIGNED NOT NULL,
  `firstname` varchar(255) CHARACTER SET utf8mb3 NOT NULL,
  `lastname` varchar(255) CHARACTER SET utf8mb3 NOT NULL,
  `user_code` varchar(50) CHARACTER SET utf8mb3 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE `user_data`
  ADD PRIMARY KEY (`user_id`) USING BTREE;
COMMIT;

-----
EXPLAIN SELECT * FROM users u
INNER JOIN user_data ud ON u.user_id = ud.user_id;


|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra||
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
|1|SIMPLE|ud|*NULL*|ALL|PRIMARY|*NULL*|*NULL*|*NULL*|315833|100.00|*NULL*|
|1|SIMPLE|u|*NULL*|eq_ref|PRIMARY|PRIMARY|4|test.ud.user_id|1|100.00|*NULL*|

Although the user_id field is the primary key in both tables, I see that the entire table is scanned when explain is made. What is the reason for this?

2

This is only for MyISAM tables.

It shouldn’t matter, but I also noticed you are using different character sets between these tables. Pick one.

I see no WHERE clause to filter rows in user_data, so it’s full table scanning. Add WHERE ud.username = ‘foo’ and it should not table scan.

1 Like