| 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!
|
|
|
|
| Re: Index optimization in Join [message #819 is a reply to message #818 ] |
Tue, 27 February 2007 08:52  |
enzuccio
Messages: 7
Registered: February 2007 |
Junior Member |
|
|
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.
Thanks,
VV
|
|
|
|
| Re: Index optimization in Join [message #821 is a reply to message #820 ] |
Tue, 27 February 2007 09:31 |
enzuccio
Messages: 7
Registered: February 2007 |
Junior Member |
|
|
Perfect!
Now the query is:
SELECT STRAIGHT_JOIN scat.subcatid, scat.catid, COUNT( * ) AS adcnt
FROM clf_ads a
INNER JOIN clf_subcats scat ON a.subcatid = scat.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
and this is the Explain:
a ref subcatid,cityid,verified,enabled,opt1 opt1 2 const,const 21699 Using where
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
Now the query take 0.15s instead of 0.8s.
Thanks,
VV
|
|
|
|
| Re: Index optimization in Join [message #825 is a reply to message #824 ] |
Tue, 27 February 2007 10:48 |
enzuccio
Messages: 7
Registered: February 2007 |
Junior Member |
|
|
Hi,
just another optimization with GROUP BY and ORDER BY:
SELECT STRAIGHT_JOIN a . * , UNIX_TIMESTAMP( a.createdon ) AS timestamp, ct.cityname, COUNT( * ) AS piccount, p.picfile, scat.subcatname, cat.catid, cat.catname
FROM clf_ads a
INNER JOIN clf_cities ct ON a.cityid = ct.cityid
INNER JOIN clf_subcats scat ON a.subcatid = scat.subcatid
INNER JOIN clf_cats cat ON scat.catid = cat.catid
LEFT OUTER JOIN clf_adxfields axf ON a.adid = axf.adid
LEFT OUTER JOIN clf_adpics p ON a.adid = p.adid
AND p.isevent = '0'
LEFT OUTER JOIN clf_featured feat ON a.adid = feat.adid
AND feat.adtype = 'A'
WHERE a.enabled = '1'
AND a.verified = '1'
AND a.expireson >= NOW( )
AND (
feat.adid IS NULL
OR feat.featuredtill < NOW( )
)
GROUP BY a.adid
ORDER BY a.createdon DESC
LIMIT 0 , 20
with the index "opt1" (enabled,verified,subcatid) I have this EXPLAIN:
--------------------------------------------------------
axf system adid NULL NULL NULL 0 const row not found
feat system adid NULL NULL NULL 0 const row not found
a ref subcatid,cityid,verified,enabled,opt1 opt1 2 const,const 22374 Using where; Using temporary; Using filesort
ct eq_ref PRIMARY PRIMARY 4 a.cityid 1 Using where
scat eq_ref PRIMARY,catid PRIMARY 4 a.subcatid 1
cat eq_ref PRIMARY PRIMARY 4 scat.catid 1
p ref adid adid 5 a.adid,const 2
--------------------------------------------------------
As you can see, mysql performs "Using temporary; Using filesort" on "a". I tried to insert an index (enabled,verified,createdon) but nothing.
Without STRAIGHT_JOIN here the Explain:
--------------------------------------------------------
axf system adid NULL NULL NULL 0 const row not found
feat system adid NULL NULL NULL 0 const row not found
scat ALL PRIMARY,catid NULL NULL NULL 83 Using temporary; Using filesort
a ref subcatid,cityid,verified,enabled,opt1 opt1 6 const,const,scat.subcatid 146 Using where
ct eq_ref PRIMARY PRIMARY 4 a.cityid 1 Using where
cat eq_ref PRIMARY PRIMARY 4 scat.catid 1
p ref adid adid 5 a.adid,const 2
--------------------------------------------------------
"Using temporary; Using filesort" is in "scat".
This query currently take about 5 secs.
Let me know.
Thanks,
VV
|
|
|
|
|
|
| Re: Index optimization in Join [message #889 is a reply to message #816 ] |
Tue, 06 March 2007 16:53 |
jenda
Messages: 1
Registered: March 2007 |
Junior Member |
|
|
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.
Thanks a lot,
Jan
|
|
|
|
Members
Search
Help
Register
Login
Home
