...

View Full Version : explain - Using index; Using temporary; Using filesort



BubikolRamios
09-26-2011, 06:31 PM
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" ""

Old Pedant
09-26-2011, 09:26 PM
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?

BubikolRamios
09-27-2011, 06:50 AM
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

Old Pedant
09-27-2011, 08:18 PM
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.

BubikolRamios
09-28-2011, 02:27 PM
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 ?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum