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.
ANALYZE TABLE
yet? - Charles