MySQL explain gives different results on different machines

user_3022127 0
Viewed 59

The following query yields two different results when I run it as opposed to when a coworker runs it:

explain select *
from `mydatabase`.`Transactions` as `transaction`
join `mydatabase`.`Documents` as `document` on `document`.`version` = `transaction`.`documentVersion`
join `mydatabase`.`DocumentTopics` as `documentTopic` on `documentTopic`.`documentId` = `document`.`documentId`
join `mydatabase`.`Topics` on `mydatabase`.`Topics`.`id` = `documentTopic`.`topicID`
where `mydatabase`.`Topics`.`id` in (1,2)
and `transaction`.`userHash` = 'xxxxx';

On my machine:

enter image description here

On my coworkers machine:

enter image description here

So why is it doing a full table scan on my machine but not on my coworkers machine?

We're both using a dockerized MySQL 5.7.33 on Mac with the exact same db dump.

How is this possible?

mysql
1 Answers

The optimizer's choice of index is based on statistics InnoDB holds for tables and indexes.

These statistics are produced periodically by InnoDB reading a random sample of pages from the table, and extrapolating things like index cardinality, so it can decide whether the index is worth using.

Occasionally the statistics are based on a skewed sampling of data, so the statistics are not representative.

I suggest you run ANALYZE TABLE Transactions in both MySQL containers, then try the experiment again. This statement will cause InnoDB to refresh the statistics for that table, based on a new random sample of pages.

Bill_Karwin_208600
Related