Is mysql's dayofweek Expensive in memory... Or I should not care?

I am trying to speedup database select for reporting with more than 3Mil data. Is it good to use dayofweek?

Query is something like this:

SELECT 
  COUNT(tblOrderDetail.Qty) AS `BoxCount`, 
  `开发者_C百科tblBoxProducts`.`ProductId` AS `BoxProducts`, 
  `tblOrder`.`OrderDate`,
  `tblFranchise`.`FranchiseId` AS `Franchise` 
FROM `tblOrder`
INNER JOIN `tblOrderDetail` ON tblOrderDetail.OrderId=tblOrder.OrderId
INNER JOIN `tblFranchise` ON tblFranchise.FranchiseeId=tblOrderDetail.FranchiseeId
INNER JOIN `tblBoxProducts` ON tblOrderDetail.ProductId=tblBoxProducts.ProductId
WHERE (tblOrderDetail.Delivered = 1) AND 
(tblOrder.OrderDate >= '2004-05-17') AND 
(tblOrder.OrderDate < '2004-05-24')
GROUP BY `tblBoxProducts`.`ProductId`,`tblFranchise`.`FranchiseId` 
ORDER BY `tblOrder`.`OrderDate` DESC

But what I really want is to show report for everyday in a week. Like On Sunday, Monday....

So Would it be a good idea to use dayofweek in query or render the result from the view?

No, using dayofweek as one of the columns you're selecting is not going to hurt your performance significantly, nor will it blow out your server memory. Your query shows that you're displaying seven distinct order_date days' worth of orders. Maybe there are plenty of orders, but not many days.

But you may be better off using DATE_FORMAT (see http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format) to display the day of the week as part of the order_date column. Then you don't have to muck around in your client turning (0..6) into (Monday..Sunday) or is it (Sunday..Saturday)? You get my point.

A good bet is to wrap your existing query in an extra one just for formatting. This doesn't cost much and lets you control your presentation without making your data-retrieval query more complex.

Note also you omitted order_date from your GROUP BY expression. I think this is going to yield unpredictable results in mySql. In Oracle, it yields an error message. Also, I don't know what you're doing with this result set, but don't you want it ordered by franchise and box products as well as date?

I presume your OrderDate columns contain only days -- that is, all the times in those column values are midnight. Your GROUP BY won't do what you hope for it to do if there are actual order timestamps in your OrderDate columns. Use the DATE() function to make sure of this, if you aren't sure already. Notice that the way you're doing the date range in your WHERE clause is already correct for dates with timestamps. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date

So, here's a suggested revision to your query. I didn't fix the ordering, but I did fix the GROUP BY expression and used the DATE() function.

SELECT BoxCount, BoxProducts, 
       DATE_FORMAT(OrderDate, '%W') AS DayOfWeek,
       OrderDate,
       Franchise
FROM (
  SELECT 
    COUNT(tblOrderDetail.Qty) AS `BoxCount`, 
    `tblBoxProducts`.`ProductId` AS `BoxProducts`, 
    DATE(`tblOrder`.`OrderDate`) AS OrderDate,
    `tblFranchise`.`FranchiseId` AS `Franchise` 
  FROM `tblOrder`
  INNER JOIN `tblOrderDetail` ON tblOrderDetail.OrderId=tblOrder.OrderId
  INNER JOIN `tblFranchise` ON tblFranchise.FranchiseeId=tblOrderDetail.FranchiseeId
  INNER JOIN `tblBoxProducts` ON tblOrderDetail.ProductId=tblBoxProducts.ProductId
  WHERE (tblOrderDetail.Delivered = 1) 
    AND (tblOrder.OrderDate >= '2004-05-17')
    AND (tblOrder.OrderDate < '2004-05-24')
  GROUP BY `tblBoxProducts`.`ProductId`,`tblFranchise`.`FranchiseId`, DATE(`tblOrder`.`OrderDate`) 
  ORDER BY DATE(`tblOrder`.`OrderDate`) DESC
) Q

You have lots of inner join operations; this query may still take a while. Make sure tblOrder has some kind of index on OrderDate for best performance.