# Does not use a primary key index

**URL:** <https://forums.percona.com/t/does-not-use-a-primary-key-index/30102>\
**Category:** MySQL & MariaDB\
**Created:** [May 7, 2024, 4:40pm UTC](https://forums.percona.com/t/does-not-use-a-primary-key-index/30102 "2024-05-07T16:40:52Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![andre53](https://avatars.discourse-cdn.com/v4/letter/a/43a26b/32.png) [@andre53](https://forums.percona.com/u/andre53)\
**Post date:** [May 7, 2024, 4:40pm UTC](https://forums.percona.com/t/does-not-use-a-primary-key-index/30102/1 "2024-05-07T16:40:52Z")

</div>

```auto
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;

```

```auto

|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?

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [May 7, 2024, 5:20pm UTC](https://forums.percona.com/t/does-not-use-a-primary-key-index/30102/2 "2024-05-07T17:20:11Z")

</div>

> [@andre53](#):
>
> `PACK_KEYS=0`

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.