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.
Hi all, i also have some problem of this kind. Do you think there is a way to optimize this query ?
SELECT id, nazev, helpid FROM params_titles WHERE id IN (16,17,18) ORDER BY ord;
I created these indexes: PRIMARY, (id, ord)
EXPLAIN said:
id: 1
select_type: PRIMARY
table: params_titles
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using where; Using filesort
The keys specified in the IN clause are automatically inserted by calling script and are always different.
What worries me the most is ‘Using filesort’.
I personally think that it is impossible to optimize queries containing the IN clause, but if you are aware of some sort of workaround please let me know, I would really appreciate.
Right. I if you have IN MySQL will not use second key part for order by.
Search out blog, I’ve posted on this problem - somethimes you can do good but using set of ordered unions with global order by (assuming you have LIMIT)