Improving slow queries. Slow query log attached

Below is the Slow Query Log when my .NET application was running. Please let me know how to improve the performance of the queries:

```
TCP Port: 3306, Named Pipe: (null)
Time Id Command Argument
# Time: 110805 13:25:39
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.015625 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1373
use stockist;
SET timestamp=1312530939;
SELECT SUM(GROSSAMOUNT) FROM BILLDETAILS WHERE MONTH(BILLDATE) = 8 AND YEAR(BILLDATE) = 2011;
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.000000 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1373
SET timestamp=1312530939;
SELECT COUNT(BILLNO) FROM BILLDETAILS WHERE MONTH(BILLDATE) = 8 AND YEAR(BILLDATE) = 2011;
# Time: 110805 13:30:32
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.078125 Lock_time: 0.046875 Rows_sent: 1 Rows_examined: 1374
SET timestamp=1312531232;
SELECT A.BILLNO AS BILL_NO, DATE_FORMAT(A.BILLDATE,'%d/%m/%Y') AS BILL_DATE, B.PARTYNAME AS PARTY_NAME, A.NETAMOUNT AS NET_AMOUNT FROM BILLDETAILS A, PARTYMASTER B WHERE A.PARTYID = B.PARTYID AND MONTH(A.BILLDATE) = 8 AND YEAR(A.BILLDATE) = 2011 ORDER BY A.BILLNO, A.BILLDATE;
# Time: 110805 13:30:44
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.296875 Lock_time: 0.031250 Rows_sent: 407 Rows_examined: 19552
SET timestamp=1312531244;
select a.itemcode as Item_Code, a.itemname as Item_Name, a.stockinhand as Stock_In_Hand, b.mrp As MRP, round((a.stockinhand * b.mrp),2) As Value, date_format(b.invoicedate,'%d/%m/%Y') As Stock_Date from itemmaster a, stockentry b where a.itemid = b.itemid and invoicedate = (select max(invoicedate) from stockentry where itemid = b.itemid) group by a.itemname order by a.itemname;
# Time: 110805 13:30:55
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.000000 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
SET timestamp=1312531255;
SELECT * FROM REMARKSETTINGS;
# User@Host: root[root] @ localhost [127.

One area that stands out is the way you are accessing the BillDetails table. All queries appear to be using MONTH and YEAR operators on BillDate. For example:

SELECT SUM(GROSSAMOUNT) FROM BILLDETAILS WHERE MONTH(BILLDATE) = 8 AND YEAR(BILLDATE) = 2011;

This approach requires accessing every record in BillDetails. A better approach would be to index BillDate and query as follows:

SELECT SUM(GROSSAMOUNT) FROM BILLDETAILS WHERE BILLDATE BETWEEN '2011-08-01' AND '2011-08-31';

Looks like you don't have much data in there at the moment (1373 rows) so the difference in the above will be minimal at the moment, more significant when the volume grows.

StackExchange Database Administrators Q#4358, answer score: 5