Index optimization in Join


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

Please can you help me?


To avoid filesort and temporary table index needs to be something like


Thanks Peter,

with an index (enabled,verified,subcatid) on “a” now I obtain

scat ALL PRIMARY,catid NULL NULL NULL 83 Using where; Using temporary; Using filesort

a ref subcatid,cityid,verified,enabled,opt1 opt1 6 const,const,scat.subcatid 145 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

Seems that it is not possible to perform the query without “Using temporary; Using filesort”.

Please let me know.



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)


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.

Thanks a lot,


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)