congratulations for your tutorials, very interesting. I need assistance with some troubles.
I need to optimize a join in order to speed up my site.
The join is:
SELECT scat.subcatid, scat.catid, COUNT(*) as adcnt
FROM clf_ads a
INNER JOIN clf_subcats scat ON scat.subcatid = a.subcatid AND a.enabled = ‘1’ AND a.verified = ‘1’ AND a.expireson >= NOW()
INNER JOIN clf_cats cat ON cat.catid = scat.catid
INNER JOIN clf_cities ct ON a.cityid = ct.cityid
WHERE scat.enabled = ‘1’
GROUP BY a.subcatid
Query took about 0.8142 sec and in my opinion it is too much slow.
Here the explain:
table type possible_keys key key_len ref rows Extra
a ref subcatid,cityid,verified,enabled verified 1 const 22614 Using where; Using temporary; Using filesort
scat eq_ref PRIMARY,catid PRIMARY 4 a.subcatid 1 Using where
cat eq_ref PRIMARY PRIMARY 4 scat.catid 1 Using index
ct eq_ref PRIMARY PRIMARY 4 a.cityid 1 Using where; Using index
Here info about the table “a”:
Keyname TypeCardinality Action Field
PRIMARY PRIMARY 23895 adid
subcatid INDEX 82 subcatid
cityid INDEX 102 cityid
verified INDEX 2 verified
enabled INDEX 2 enabled
I tried to avoid “Using temporary; Using filesort” on a creating an index for “a” on verified-cityid-subcatid but the performance is the same and “Using temporary; Using filesort” is switched to the table ct.