MySQL query optimization JOIN

Question

I need your help to optimize those mysql query, both are in my slow query logs.

SELECT a.nom, c.id_apps, c.id_commentaire, c.id_utilisateur,
       c.note_commentaire, u.nom_utilisateur
  FROM comments AS c
  LEFT JOIN apps AS a ON c.id_apps = a.id_apps
  LEFT JOIN users AS u ON c.id_utilisateur = u.id_utilisateur
 ORDER BY c.date_commentaire DESC LIMIT 5;

There is a MySQL INDEX on c.id_apps, a.id_apps, c.id_utilisateur, u.id_utilisateur and c.date_commentaire.

EXPLAIN result:

id | select_type | table |  type  | possible_keys | key              | key_len | ref              | rows | Extra
1  | SIMPLE      |   c   |  index | NULL          | date_commentaire | 8       | NULL             | 119  |
1  | SIMPLE      |   a   | eq_ref | PRIMARY       | PRIMARY          | 3       | c.id_apps        | 1    |
1  | SIMPLE      |   u   | eq_ref | PRIMARY       | PRIMARY          | 3       | c.id_utilisateur | 1    |

SELECT a.id_apps, a.id_itunes, a.nom, a.prix, a.resume, c.nom_fr_cat, e.nom_edit
  FROM apps AS a
  LEFT JOIN cat AS c ON a.categorie = c.id_cat
  LEFT JOIN edit AS e ON a.editeur = e.id_edit
 ORDER BY a.id_apps DESC LIMIT 20;

EXPLAIN result:

id | select_type | table |  type  | possible_keys |   key   | key_len | ref         | rows | Extra
1  | SIMPLE      |   a   |  index | NULL          | PRIMARY |    3    | NULL        | 5336 |  
1  | SIMPLE      |   c   | eq_ref | PRIMARY       | PRIMARY |    1    | a.categorie |  1   |  
1  | SIMPLE      |   e   | eq_ref | PRIMARY       | PRIMARY |    3    | a.editeur   |  1   |

There is a MySQL INDEX on a.categorie, c.id_cat, a.editeur, e.id_edit and a.id_apps

Thanks

Pierre, don't try to syntax-highlight by hand. Just format it as code (indent four spaces) and format it nicely. I've done it for you in this instance. — Marcelo Cantos
How large are the respective data sets? I notice there are no WHERE clauses; do you really want all comments and apps returned? Are the LIMIT clauses part of the design, or did you add them while testing? — Marcelo Cantos
Thanks guys & sorry Marcelo it's my first time on Stackoverflow The first query with EXPLAIN: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE c index NULL date_commentaire 8 NULL 119 1 SIMPLE a eq_ref PRIMARY PRIMARY 3 c.id_apps 1 1 SIMPLE u eq_ref PRIMARY PRIMARY 3 c.id_utilisateur 1 And the second id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE a index NULL PRIMARY 3 NULL 5336 1 SIMPLE c eq_ref PRIMARY PRIMARY 1 a.categorie 1 1 SIMPLE e eq_ref PRIMARY PRIMARY 3 a.editeur 1 There is no WHERE clause but it's normal and the a LIMIT — Pierre

Pierre Pierre · Accepted Answer · 2010-05-01T09:39:03

Thanks guys & sorry Marcelo it's my first time on Stackoverflow

The first query with EXPLAIN:

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE c index NULL date_commentaire 8 NULL 119
1 SIMPLE a eq_ref PRIMARY PRIMARY 3 c.id_apps 1
1 SIMPLE u eq_ref PRIMARY PRIMARY 3 c.id_utilisateur 1

And the second

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE a index NULL PRIMARY 3 NULL 5336
1 SIMPLE c eq_ref PRIMARY PRIMARY 1 a.categorie 1
1 SIMPLE e eq_ref PRIMARY PRIMARY 3 a.editeur 1

MySQL query optimization JOIN

1 Answers