MySQL Performance: MySQL => Index optimization in Join

Index optimization in Join [message #816]

Tue, 27 February 2007 05:06

enzuccio
Messages: 7
Registered: February 2007

Junior Member

Hi,

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.

Please can you help me?

Thanks!

Report message to a moderator