explain - Using index; Using temporary; Using filesort

I guess this slows down whole thing considerably. Any comments, suggestions ?


Select *
FROM galery_1 g join galery_optional_inf optional_inf on g.id_galery = optional_inf.id_galery



"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "SIMPLE" "optional_inf" "index" "PRIMARY" "PRIMARY" "8" NULL "5017" "Using index"
"1" "SIMPLE" "g" "eq_ref" "PRIMARY" "PRIMARY" "4" "test.optional_inf.id_galery" "1" ""





Select *
FROM galery_1 g join galery_optional_inf optional_inf on g.id_galery = optional_inf.id_galery
group by g.id_galery



"select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "id" "Extra"
"SIMPLE" "optional_inf" "index" "PRIMARY" "PRIMARY" "8" NULL "5017" "1" "Using index; Using temporary; Using filesort"
"SIMPLE" "g" "eq_ref" "PRIMARY" "PRIMARY" "4" "test.optional_inf.id_galery" "1" "1" ""

Not sure what the point of the GROUP BY is.

Surely id_galery is the primary key of table galery_1??

So I can't see that doing GROUP BY would do anything at all, since presumably there would always only be single values of id_galery in that table?

id_galery is primary, sql is extract of bigger sql, where group by make sence and extract still produces Using index; Using temporary; Using filesort

Okay, that makes sense. I don't know why MySQL thinks it needs a filesort in the simple example you gave, but I can understand why it would need one in a more complex situation.

I read somewhere, and tested it, that, if you do "order by null" after group by , filesort goes away. But it only works if there is no limit included.

Anyway I remember when using mssql there was a tool that told you where to put indexes based on given query. Anything like that, free if possible, outthere for mysql ?