# Problems with migration

**URL:** <https://forums.percona.com/t/problems-with-migration/588>\
**Category:** Other MySQL® Questions\
**Created:** [January 7, 2008, 5:14pm UTC](https://forums.percona.com/t/problems-with-migration/588 "2008-01-07T17:14:07Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![enzuccio](https://avatars.discourse-cdn.com/v4/letter/e/d9b06d/32.png) [@enzuccio](https://forums.percona.com/u/enzuccio)\
**Post date:** [January 7, 2008, 5:14pm UTC](https://forums.percona.com/t/problems-with-migration/588/1 "2008-01-07T17:14:07Z")

</div>

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.