1
votes

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.

1
What are the indices on the source tables in both databases? - unique2
Indices are o.id,oi.id,os.id,o.statusid,s.id,o.storeid,c.id,o.customerid and a.id. They are same for both databases because I copied the whole database from old server to new one. I also tried analyze the tables etc, but didn't work. - user2804844
Have you compared the explain on both servers? - unique2
I've just added comparison result to the question. Thank you. - user2804844
Have you run an ANALYZE TABLE yet? - Charles

1 Answers

0
votes

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