Hi,
due to a server migration, we have done a migration of a LAMP applications. We exported an entire database from the version 5.0.27-standard-log. This application contains some heavy queries, but thanks to some indexes they were light. Here an example:
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 force INDEX(opt3)
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 scat.catid = 10
AND a.enabled = ‘1’ AND a.verified = ‘1’ AND a.expireson >= ‘2003-07-18 18:00:00’
AND (feat.adid IS NULL OR feat.featuredtill < ‘2010-07-18 18:00:00’)
AND ct.countryid = 1
GROUP BY a.adid DESC
LIMIT 0, 30;
with the index opt3 the query takes about 0.001 secs with a table of about 200,000 items.
We migrated the LAMP application on a server with the version 5.0.45-community-log. everything was recreated, including indexes.
Now with the same query the db doesn’t use any index and takes about 30 secs. With the previous mysql I got the following explain:
1 SIMPLE axf system adid NULL NULL NULL 0 const row not found
1 SIMPLE feat system adid NULL NULL NULL 0 const row not found
1 SIMPLE cat const PRIMARY PRIMARY 4 const 1
1 SIMPLE a index NULL PRIMARY 4 NULL 138495 Using where
1 SIMPLE ct eq_ref PRIMARY,countryid PRIMARY 4 annunci_annuncinet.a.cityid 1 Using where
1 SIMPLE scat eq_ref PRIMARY,catid PRIMARY 4 annunci_annuncinet.a.subcatid 1 Using where
1 SIMPLE p ref adid adid 5 annunci_annuncinet.a.adid,const 2
Now this explain:
1 SIMPLE axf system adid NULL NULL NULL 0 const row not found
1 SIMPLE feat system adid NULL NULL NULL 0 const row not found
1 SIMPLE cat const PRIMARY PRIMARY 4 const 1 Using temporary; Using filesort
1 SIMPLE a ALL NULL NULL NULL NULL 138669 Using where
1 SIMPLE ct eq_ref PRIMARY,countryid PRIMARY 4 net.a.cityid 1 Using where
1 SIMPLE scat eq_ref PRIMARY,catid PRIMARY 4 net.a.subcatid 1 Using where
1 SIMPLE p ref adid adid 5 net.a.adid,const 2
I tried with the same file of configuration, but nothing: runnings are very different, bu the query and indexes are the same. Moreover I tried to recreate indexes.
I don’t know how to solve it, any suggestion? Seems that in the new version the system is not able to detect indexes.