Report result - Can SQL be improved?

I am creating a report of Shops Order. SQL Query does work as expected.

It calulate the numbers of orders from each shop, the total cost of orders and our commission and the outstanding fields.

If the customer paid by Cash at the delivery, then it show OutstandingComm field If the customer paid by Card from Online, then it show outstanding_shop field

SELECT T.ShopID, T.company, O.order_id, count(*) as NumOfOrders,开发者_如何学运维 
       sum(O.shop_remaining) as ShopEarnings, 
       sum(O.comm_grandtotal) as OurComm, 
       SUM(CASE WHEN payment_method = 'PayCash' AND status = 1 THEN O.comm_grandtotal ELSE 0 END) as OutstandingComm,
       SUM(CASE WHEN payment_method = 'PayCard' AND status = 1 THEN O.shop_remaining ELSE 0 END) as outstanding_shop
FROM Shops as T 
      JOIN orders O ON O.ShopID = T.ShopID 
Group by ShopID

Can this SQL query could be improved or is there alternative better way?

---

Few things I can think of:
Here are some things to consider, try them if you feel like it, but be sure to time the effect of each change, it might not work or it might make things slower:

1. The case when looks at a string (PayCash/PayCard). If you make that field a number, put an index on it and check the numeric value, it might run faster. The index might not get used though because it has a low cardinality.
2. If you use InnoDB, try and put an index on shops.company. This way MySQL can use the covering index for shops.company to retrieve the companyname and doesn't need to do a full table read on table shops.
3. You might consider partitioning the tables by field status. Status has low cardinality, so an index will probably not be used, but if you partition, MySQL will only read from the status=1 version of the table, only do this when there are a lot of status=0 fields.
4. You are currently selecting all transactions, this is a bit unlikely. Partition the table by year or month and select only the current year or month.
5. I'm sure you already have a primary autoincrement keys on shops.shopid.
6. You should also have an index on orders.shopid

---

Your SQL already looks good. You are already doing the best you can for your design.

---

Sound like a Maxwell House coffee guy...to the last drop!...looks good to me, however, consider a WHERE clause as data may grow in the future...could do the same by dropping an AND on the current JOIN.