i am having 3 tables,

testdata1: id (pri)                      -> 1000000 rows
testdata2: id (pri), channel (indexed),  -> 10000 rows
testdata3: id (pri)                      -> 1000 rows

on performing following query, i get scan on testdata2.

explain format=tree 
select * 
from testdata1 
inner join testdata2 on testdata1.id = testdata2.channel 
inner join testdata3 on testdata2.channel = testdata3.id 
where testdata1.id < 100;

EXPLAIN: -> Nested loop inner join  (cost=8014.20 rows=9984)
    -> Nested loop inner join  (cost=4519.80 rows=9984)
        -> Table scan on testdata2  (cost=1025.40 rows=9984)
        -> Filter: ((testdata1.id < 100) and (testdata1.id = testdata2.`channel`))  (cost=0.25 rows=1)
            -> Single-row index lookup on testdata1 using PRIMARY (id=testdata2.`channel`)  (cost=0.25 rows=1)
    -> Filter: (testdata2.`channel` = testdata3.id)  (cost=0.25 rows=1)
        -> Single-row index lookup on testdata3 using PRIMARY (id=testdata2.`channel`)  (cost=0.25 rows=1)

why is mysql not utilising index of testdata2(channel) column?

*UPDATE

After running analyze table testdata2, mysql used the index. Is it necessary to use analyze table command after creating index?

EXPLAIN: -> Nested loop inner join  (cost=241.34 rows=156)
    -> Nested loop inner join  (cost=186.85 rows=156)
        -> Filter: (testdata1.id < 100)  (cost=20.09 rows=99)
            -> Index range scan on testdata1 using PRIMARY over (id < 100)  (cost=20.09 rows=99)
        -> Index lookup on testdata2 using a_temp_index (channel=testdata1.id), with index condition: (testdata1.id = testdata2.`channel`)  (cost=1.53 rows=2)
    -> Filter: (testdata2.`channel` = testdata3.id)  (cost=0.25 rows=1)
        -> Single-row index lookup on testdata3 using PRIMARY (id=testdata2.`channel`)  (cost=0.25 rows=1)

CodePudding user response：

https://dev.mysql.com/doc/refman/8.0/en/create-index.html says:

When the innodb_stats_persistent setting is enabled, run the ANALYZE TABLE statement for an InnoDB table after creating an index on that table.

This setting is on by default, so yes, it's recommended to run ANALYZE TABLE after creating an index.