Is MySql composite index hit for this sql?

Question

I have one table: student_homework, and one of its composite index is uk_sid_lsnid_version(student_id, lesson_id, curriculum_version, type):

student_homework    0   uk_sid_lsnid_version    1   student_id  A   100             BTREE       
student_homework    0   uk_sid_lsnid_version    2   lesson_id   A   100             BTREE       
student_homework    0   uk_sid_lsnid_version    3   curriculum_version  A   100             BTREE       
student_homework    0   uk_sid_lsnid_version    4   type    A   100             BTREE   

Now i have a Sql: select * from student_homework where student_id=100 and type=1 and explain result is like:

1   SIMPLE  student_homework        ref uk_sid_lsnid_version,idx_student_id_update_time uk_sid_lsnid_version    4   const   20  10.0    Using index condition

The execution plan is uk_sid_lsnid_version.

The question for me is how the query condition type works here? Does DB engine scans all (narrowed) records for it? In my understanding, the tree hierarchy architecture is:

              student_id 
               /       \
           lesson_id     lesson_id
            /                      \  
     curriculum_version          curriculum_version
       /            \
      type         type

For the query condition (student_id, type), student_id matches the root of the tree index. Yet, the type does not match index lesson_id, the DB engine would apply type on all records, which have been filted by student_id.

1. Is my understanding is correct? if the subset records with a student_id is large, the query cost is still expensive.
2. There is no difference between query condition student_id = 100 and type =0 and type=0 and student_id = 100
3. To make full usage of composite index, would it be better if I add a new composite index (student_id, type)?

Answer 1

Yes, your understanding is correct, mysql will use uk_sid_lsnid_version index to match on student_id only, while filtering on type will be done a on the reduced set of rows that match on student_id.

The hint is in the extra column of the explain result: Using index condition

Using index condition (JSON property: using_index_condition)

Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer (“push down”) reading full table rows unless it is necessary. See Section 8.2.1.6, “Index Condition Pushdown Optimization”.

Section 8.2.1.6, “Index Condition Pushdown Optimization describes the steps of this technique as:

1. Get the next row's index tuple (but not the full table row).
2. Test the part of the WHERE condition that applies to this table and can be checked using only index columns. If the condition is not satisfied, proceed to the index tuple for the next row.
3. If the condition is satisfied, use the index tuple to locate and read the full table row.
4. Test the remaining part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

Whether it would be better to add another composite index on student_id, type is a question that cannot be objectively answered by us, you need to test it.

If the speed of the query with the current index is fine, then you probably do not need a new index. You also need to weigh in how many other queries would use that index - there is not much point to create an index just for one query. You also need to weigh in how selective the type field is. Type fields with a limited list of values are often not selective enough. Mysql may decide to use index condition pushdown since student_id, type index is a not a covering index and mysql would have to get the full row anyway.