Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts

    explain - Using index; Using temporary; Using filesort

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

    Code:
    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" ""


    Code:
    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" ""
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,899
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    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?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    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
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,899
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    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 ?
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •