Need index for simple query please

Question

Can anyone suggest a good index to make this query run quicker?

SELECT 
    s.*, 
    sl.session_id AS session_id, 
    sl.lesson_id AS lesson_id
FROM 
    cdu_sessions s
INNER JOIN cdu_sessions_lessons sl ON sl.session_id = s.id
WHERE  
    (s.sort = '1') AND 
    (s.enabled = '1') AND 
    (s.teacher_id IN ('193', '1', '168', '1797', '7622', '19951'))

Explain:

id | select_type | table | partitions | type | possible_keys  | key  | key_len | ref                | rows | filtered  | Extra
1  | SIMPLE      | s     | NULL       | ALL  | PRIMARY        | NULL | NULL    | NULL               | 2993 | 0.50      | Using where
1  | SIMPLE      | sl    | NULL       | ref  | session_id,ix2 | ix2  | 4       | ealteach_main.s.id | 5    | 100.00    | Using index

cdu_sessions looks like this:

------------------------------------------------
id                            | int(11)
name                          | varchar(255)
map_location                  | enum('classroom', 'school'...)
sort                          | tinyint(1)
sort_titles                   | tinyint(1)
friend_gender                 | enum('boy', 'girl'...)
friend_name                   | varchar(255)
friend_description            | varchar(2048)
friend_description_format     | varchar(128)
friend_description_audio      | varchar(255)
friend_description_audio_fid  | int(11)
enabled                       | tinyint(1)
created                       | int(11)
teacher_id                    | int(11)
custom                        | int(1)
------------------------------------------------

cdu_sessions_lessons contains 3 fields - id, session_id and lesson_id

Thanks!

Answer 1

Without looking at the query plan, row count and distribution on each table, is hard to predict a good index to make it run faster.

But, I would say that this might help:

> create index sessions_teacher_idx on cdu_sessions(teacher_id);