Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Network duration/SQL_CALC_FOUND_ROWS

BubikolRamios's profile image BubikolRamios posted 5 years ago in General Permalink
SET SESSION query_cache_type=0;
select SQL_CALC_FOUND_ROWS g.id_galery_obfuscated,,g.id_tezaver,g.title,g.height_px_thumb,g.width_px_thumb,t.id_tezaver_obfuscated,SUBSTRING_INDEX(group_concat(t.term order by t.eunis ),',',1) as term,ttxs.source as taxonomy_src, breed_cultivar.breed_cultivar,group_concat(distinct t1.term) as term_g 
from galery_1 g 
left join tezaver t on g.id_tezaver = t.id_tezaver and t.l2 = 'la'  
left join tezaver_taxonomy_source ttxs on g.id_tezaver = ttxs.id_tezaver  
join tezaver t1 on g.id_tezaver = t1.id_tezaver and t1.l2 = 'la'  
left join galery_1_breed_cultivar breed_cultivar on g.id_galery = breed_cultivar.id_galery  
where 1 = 1 and  (g.image_quality >= 1 or g.image_quality is null) 
group by  g.id_galery
having   term_g like '%agaricus%'  
order by g.id_galery desc
limit 0,18

/* Affected rows: 0  Found rows: 18  Warnings: 0  Duration for 2 queries: 0,280 sec. (+ 34,118 sec. network) */

How is that network 34 sec for 18 records? I'm sure it is not.

Removing SQL_CALC_FOUND_ROWS from query does it all together in 1.5 sec.
ansgar's profile image ansgar posted 5 years ago Permalink
SQL_CALC_FOUND_ROWS prefetches all rows, anyway if you have a LIMIT clause or not.
BubikolRamios's profile image BubikolRamios posted 5 years ago Permalink
Fetches where ? Inside server I suppose. Since I'm doing
SELECT FOUND_ROWS() on server. Hence I think.

"+ 34,118 sec. network" is wrong information

anyway, if I remove limit and SQL_CALC_FOUND_ROWS the message is the same: "+ 34,118 sec. network" for total < 600 records.

/* Affected rows: 0 Found rows: 559 Warnings: 0 Duration for 2 queries: 0,297 sec. (+ 34,195 sec. network) */

Wrong. It did not take to trasfer 600 records, the lag was on server sql processing.

Please login to leave a reply, or register at first.