Mysql - the same query is much more slower in better server

Question

I've a query like this:

SELECT o.id,
        o.archieve,
        listingid,
        orderdate,
        paymentdate,
        os.STATUS,
        o.shippingpreference,
        s.NAME AS store,
        c.fullname AS customer,
        (
            SELECT COUNT(oi.id)
            FROM orders_inventory oi
            WHERE orderid = o.id
        ) AS itemcount,
        (
            SELECT COUNT(op.orderid)
            FROM orders_preorder op
            WHERE op.orderid = o.id
        ) AS itemcountPre,
        a.fullname AS salesrep,
        fOrderProfit(o.id) AS profit,
        o.packtime,
        o.trackingnumber,
        fSentMailToVendor(o.id) AS sentmailtovendors,
        a2.fullname AS adminname
FROM    orders o
        LEFT JOIN orders_status os
                ON os.id = o.statusid
        LEFT JOIN stores s
                ON s.id = o.storeid
        LEFT JOIN customers c
                ON c.id = o.customerid
        LEFT JOIN admins a
                ON a.id = o.salerepresentativeid
        LEFT JOIN admins a2
                ON a2.id = o.adminid
WHERE TRUE AND archieve = '0'
GROUP BY o.id
ORDER BY o.id DESC LIMIT 50

Yes, it is a little bit complicated and maybe it should be optimized. But my question is, the same query runs in an older server (mysql v5.5) in 0,4sec while it runs in 300sec in a server with two CPU and better hardware (mysql v5.6). Any ideas?

---

Using EXPLAIN EXTENDED on both servers give 8 rows of explanation but I believe the first rows make the difference, so I only listed first rows comparison:

Old Server:

• select_type: PRIMARY
• table: o
• type: index
• possible_keys: Null
• key: PRIMARY
• key_len: 4
• ref: Null
• rows: 50
• filtered: 102776
• Extra: Using where

---

New Server:

• select_type: PRIMARY
• table: o
• type: ALL
• possible_keys: PRIMARY,id,orderdate,customerid,storeid
• key: Null
• key_len: Null
• ref: Null
• rows: 51664
• filtered: 100
• Extra: Using where; Using temporary; Using filesort

Note: By the way, I converted table types in new server to InnoDB before using EXPLAIN EXTENDED.

Answer 1

I am assuming the table structures are the same and I have a good idea what it may be:

The optimizer on 5.6 is very different than the one in 5.5. You should run that statement with EXPLAIN and see what the optimizer is giving you.

It is possible that you may need to play with the optimizer_switch (turning settings on or off) or you can add USE INDEX to your statement to tell the optimizer which path to choose.

Alternatively, you can just run the query many many times and "teach" the optimizer which path to take, but that is certainly not guaranteed.

I hope that helps.

Addition with new data:

As your explain statement shows, try something like this:

SELECT o.id,
        o.archieve,
        listingid,
        orderdate,
        paymentdate,
        os.STATUS,
        o.shippingpreference,
        s.NAME AS store,
        c.fullname AS customer,
        (
            SELECT COUNT(oi.id)
            FROM orders_inventory oi
            WHERE orderid = o.id
        ) AS itemcount,
        (
            SELECT COUNT(op.orderid)
            FROM orders_preorder op
            WHERE op.orderid = o.id
        ) AS itemcountPre,
        a.fullname AS salesrep,
        fOrderProfit(o.id) AS profit,
        o.packtime,
        o.trackingnumber,
        fSentMailToVendor(o.id) AS sentmailtovendors,
        a2.fullname AS adminname
FROM    orders o use index (primary) -- new change here
        LEFT JOIN orders_status os
                ON os.id = o.statusid
        LEFT JOIN stores s
                ON s.id = o.storeid
        LEFT JOIN customers c
                ON c.id = o.customerid
        LEFT JOIN admins a
                ON a.id = o.salerepresentativeid
        LEFT JOIN admins a2
                ON a2.id = o.adminid
WHERE TRUE AND archieve = '0'
GROUP BY o.id
ORDER BY o.id DESC LIMIT 50