How to do Query Optimization in MySQL?

SELECT scat.subcatid, scat.catid, COUNT(*) as adcnt
FROM xzclf_ads a USE INDEX (adsIndex)
INNER JOIN xzclf_subcats scat USE INDEX (subcatsIndex) ON scat.subcatid = a.subcatid AND (a.enabled = '1' AND a.verified = '1' AND a.expireson >= NOW())
INNER JOIN xzclf_cats cat USE INDEX(catidIndex) ON cat.catid = scat.catid
INNER JOIN xzclf_cities ct USE INDEX(cityidIndex) ON a.cityid = ct.cityid
WHERE scat.enabled = '1'
AND ct.countryid = 1
GROUP BY a.subcatid;
Could some one optimize this query. This query is taking more time to execute.

 

Without watching the schema it can not be determined properly.

How frequent you run this query?? If you can cache the result of about 1 hour at least it'll be a great improvement.
Execute this query and save the result to another table.
Then if the table is 1 hour old update the table. This can be done by a cron.

 

i change your query

SELECT scat.subcatid, scat.catid, COUNT(*) as adcnt
FROM xzclf_ads a USE INDEX (adsIndex)
INNER JOIN xzclf_subcats scat USE INDEX (subcatsIndex)
INNER JOIN xzclf_cats cat USE INDEX(catidIndex) ON cat.catid = scat.catid
ON scat.subcatid = a.subcatid
AND (a.enabled = '1' AND a.verified = '1'
AND a.expireson >= NOW())
INNER JOIN xzclf_cities ct USE INDEX(cityidIndex) ON a.cityid = ct.cityid
WHERE scat.enabled = '1'
AND ct.countryid = 1
GROUP BY a.subcatid;

 

second solution

SELECT scat.subcatid, scat.catid, COUNT(*) as adcnt
FROM xzclf_ads a USE INDEX (adsIndex)
INNER JOIN xzclf_subcats scat USE INDEX (subcatsIndex)
INNER JOIN xzclf_cats cat USE INDEX(catidIndex) ON cat.catid = scat.catid
ON scat.subcatid = a.subcatid
AND (a.enabled = '1' AND a.verified = '1'
AND a.expireson >= NOW())
AND scat.enabled = '1'
INNER JOIN xzclf_cities ct USE INDEX(cityidIndex)
ON a.cityid = ct.cityid
AND ct.countryid = 1
GROUP BY a.subcatid;

 

Are the fields you're looking at in the indexes you're using? If not, you're doing a sequential lookup, and it'll be slow.