mysql range query , use index judge range large

Question

mysql table struct

it make me confuse, if query range influence use index in mysql !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Answer 1

in brief, i use mysql database execute EXPLAIN SELECT * FROM t_teacher_course_info WHERE teacher_id >1 and teacher_id < 5000 will use index INDEX `idx_teacher_id_last_update_time` (`teacher_id`, `last_update_time`)

but if change range EXPLAIN SELECT * FROM t_teacher_course_info WHERE teacher_id >1 and teacher_id < 10000 id select_type table type possible_keys key key_len ref rows Extra

1 1 SIMPLE t_teacher_course_info ALL idx_teacher_update_time 671082 Using where

scan all table, not use index , any mysql config maybe scan row count judge if use index. !!!!!!!

Answer 2

That is what happens. And it is actually an optimization.

When using a secondary key (such as INDEX(teacher_id)), the processing goes like this:

1. Reach into the index, which is a B+Tree. In such a structure, it is quite efficient to find a particular value (such as 1) and then scan forward (until 5000 or 10000).
2. For each entry, reach over into the data to fetch the row (SELECT *). This uses the PRIMARY KEY, a copy of which is in the secondary key. The PK and the data are clustered together; each lookup by one PK value is efficient (again, a BTree), but you need to do 5000 or 10000 of them. So the cost (time taken) adds up.

A "table scan" (ie, not using any INDEX) goes like this:

1. Start at the beginning of the table, walk through the B+Tree for the table (in PK order) until the end.
2. For each row, check the WHERE clause (a range on teacher_id).

If more than something like 20% of the table needs to be looked at, a table scan is actually faster than bouncing back and forth between the secondary index and the data.

So, "large" is somewhere around 20%. The actual value depends on table statistics, etc.

Bottom line: Just let the Optimizer do its thing; most of the time it knows best.