5
votes

Besoin d'un index pour une requête simple s'il vous plaît

Quelqu'un peut-il suggérer un bon index pour accélérer l'exécution de cette requête?

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

Expliquez:

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 ressemble à ceci:

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

cdu_sessions_lessons contient 3 champs - id, session_id et leçon_id

Merci!

sql mysql

3 commentaires

Pouvez-vous inclure la définition de table pour cdu_sessions ? Nous pourrions avoir besoin de voir ceci pour rendre une réponse ici.

publier une sortie EXPLAIN également ..

Ont ajouté à op

3 Réponses :

2
votes

Sans regarder le plan de requête, le nombre de lignes et la distribution sur chaque table, il est difficile de prédire un bon index pour le rendre plus rapide.

Mais je dirais que cela pourrait aider:

> create index sessions_teacher_idx on cdu_sessions(teacher_id);

3 commentaires

Comment cela aiderait-il étant donné que l'OP sélectionne toutes les colonnes de la table cdu_sessions ? MySQL utiliserait-il même probablement cet index?

L'index aidera à réduire le nombre de lignes , pas les colonnes. Le SGBDR chargera moins de pages à partir du disque pour exécuter la requête. Il peut utiliser un index comme celui-ci car il y a une partie de la clause where est s.teacher_id in (...) .

Ont modifié op

1
votes

en regardant dans quelles conditions vous pourriez utiliser un index composite pour la table cdu_sessions

create index idx2 on cdu_sessions_lessons(session_id, lesson_id);

et en cherchant à rejoindre et à sélectionner la table cdu_sessions_lessons

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

2 commentaires

L'ordre des colonnes dans la clause WHERE est le suivant: (sort, enabled, teacher_id) . Je pense que l'index composite doit être dans le même ordre pour que MySQL puisse bénéficier de cet index?

non @kav vous voulez une sélectivité d'index .. teacher_id est un tri INT vs, activé qui sont TINYINT, ce qui signifie que l'enseignant_id peut avoir des valeurs beaucoup plus uniques qu'un index peut filtrer .. Quelle est la raison pour laquelle il est en premier dans l'index .. Outre l'ordre dans le WHERE n'a pas d'importance L'optimiseur de MySQL est suffisamment intelligent pour savoir WHERE teacher_id IN (1, 2, 3) AND sort = 1 AND enabled = 1 et WHERE sort = 1 AND enabled = 1 AND teacher_id IN (1, 2, 3) peut utiliser ce cdu_sessions (teacher_id, sort , activé) index

0
votes

Tout d'abord, écrivez la requête afin qu'aucune conversion de type ne soit nécessaire. Toutes les comparaisons de la clause where sont des nombres, utilisez donc des constantes numériques:

create index idx_cdu_sessions_lessons_2 on cdu_sessions_lessons(session_id, lesson_id);

Bien que cela ne se produise peut-être pas dans ce cas précis, le mélange de types peut empêcher le utilisation d'index.

J'ai supprimé la colonne comme alias ( as session_id par exemple). Celles-ci étaient redondantes car le nom de la colonne est l'alias et la requête ne modifiait pas le nom.

Pour cette requête, regardez d'abord la clause WHERE . Toutes les références de colonne proviennent d'une table. Ceux-ci devraient aller dans l'index, avec les comparaisons d'égalité en premier:

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

J'ai ajouté id car il est également utilisé dans le JOIN . Formellement, id n'est pas nécessaire dans l'index s'il s'agit de la clé primaire. Cependant, j'aime être explicite si je le veux ici.

Ensuite, vous voulez un index pour la deuxième table. Seules deux colonnes sont référencées à partir de là, elles peuvent donc toutes deux aller dans l'index. La première colonne doit être celle utilisée dans le join:

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

0 commentaires