Sql_calc_found_rows

[BubikolRamios]

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.

[Old Pedant]

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?

[BubikolRamios]

Looks all sorted out I think.

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

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

[Old Pedant]

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.

[BubikolRamios]

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.

[Old Pedant]

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.