CODEWITHSUNDEEP
mysqlsql
edward
edward

Reputation: 63

Need index for simple query please

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!

Upvotes: 5

Views: 69

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

First, write the query so no type conversions are necessary. All the comparisons in the where clause are to numbers, so use numeric constants:

SELECT s.*,
       sl.session_id,  -- unnecessary because s.id is in the result set
       sl.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);

Although it might not be happening in this specific case, mixing types can impede the use of indexes.

I removed the column as aliases (as session_id for instance). These were redundant because the column name is the alias and the query wasn't changing the name.

For this query, first look at the WHERE clause. All the column references are from one table. These should go in the index, with the equality comparisons first:

create index idx_cdu_sessions_4 on cdu_sessions(sort, enabled, teacher_id, id)

I added id because it is also used in the JOIN. Formally, id is not needed in the index if it is the primary key. However, I like to be explicit if I want it there.

Next you want an index for the second table. Only two columns are referenced from there, so they can both go in the index. The first column should be the one used in the join:

create index idx_cdu_sessions_lessons_2 on cdu_sessions_lessons(session_id, lesson_id);

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

looking at where condition you could use a composite index for table cdu_sessions 

create index idx1 on cdu_sessions(teacher_id, sort, enabled);

and looking to join and select for table cdu_sessions_lessons

create index idx2 on cdu_sessions_lessons(session_id, lesson_id);

Upvotes: 1

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181300

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);

Upvotes: 2

Related Questions