GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Problems with migration

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problems with migration

    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.
Working...
X