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 6 of 6
  1. #1
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,994
    Thanks
    120
    Thanked 76 Times in 76 Posts

    Sql_calc_found_rows

    Damn, just found out what slowing down my query big time !


    Duration for 1 query: 0,141 sec. / 2,012 sec
    not having SQL_CALC_FOUND_ROWS versus having it

    Any suggestion, workaround ?

    There are only like 8000 records in total.
    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
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    well, it's not surprising that SQL_CALC_FOUND_ROWS makes it a lot slower.

    After all, now MySQL has to actually go find *ALL* the matching rows and count them...and then throw away all those outside of your LIMIT.

    Still...2 seconds for only 8000 records? It sounds to me like you have some other problem in there. Maybe missing an important index?
    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,994
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Looks all sorted out I think.

    Code:
    select  SQL_CALC_FOUND_ROWS  g.*,t.id_tezaver_obfuscated,
    substring_index(group_concat(t.term order by t.eunis ),',',1) as term,
    
    (select group_concat(distinct tax.source) 
    from tezaver_taxonomy tax 
    where tax.id_tezaver = t.id_tezaver 
    group by  tax.id_tezaver ) as taxonomy_src,
    
    (select count(t2.term) 
    from tezaver t2
    where t2.id_tezaver = g.id_tezaver and t2.l2 <> 'la') as c 
    
    from galery_1 g 
    left join tezaver t on g.id_tezaver = t.id_tezaver and t.l2 = 'la'  
    where 1 = 1 
    group by  g.id_galery  
    order by g.id_galery desc
    Code:
    "id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
    "1","PRIMARY","g","index",\N,"PRIMARY","4",\N,"7882",""
    "1","PRIMARY","t","ref","id_l2,Index_3","id_l2","16","test.g.id_tezaver,const","1",""
    "3","DEPENDENT SUBQUERY","t2","ref","id_l2,Index_3","id_l2","8","func","2","Using where"
    "2","DEPENDENT SUBQUERY","tax","ref","PRIMARY,Index 2","PRIMARY","8","func","7","Using where"
    Last edited by BubikolRamios; 01-08-2013 at 09:09 AM.
    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
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    LOL! WOW! No wonder it took you a while to get the index usage right!

    I'm amazed you get performance as good as you said!

    Nice work.
    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,994
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Anyway, I 'm thinking, not knowing what SQL_CALC_FOUND_ROWS i acualy doing in behind, that it should do following to sql (to get what it gets):
    1. remove everything between first SELECT and last FROM, and fill that with only one smat field
    2. remove all order by
    3. 1 and 2 only in case there is no HAVING

    as all that does not influence count.

    I think it does not do that now.
    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

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    I don't think MySQL is that smart.

    I think all it really does is, first, ignore the LIMIT to get *all* records. And only then in applies the LIMIT to what gets returned. That way, it can count *all* the rows that WOULD be returned. Very primitive but it works. Just not fast.
    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.


  •  

    Posting Permissions

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