Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.0.0.1]
# Query_time: 0.000000  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1312531255;
SELECT * FROM BILLINGSETTINGS;
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.015625  Lock_time: 0.015625 Rows_sent: 1  Rows_examined: 1373
SET timestamp=1312531255;
SELECT MAX(BILLNO) FROM BILLDETAILS WHERE YEAR(BILLDATE) = 2011;
# Time: 110805 13:30:58
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.031250  Lock_time: 0.000000 Rows_sent: 184  Rows_examined: 3266
SET timestamp=1312531258;
SELECT B.RECORDID, A.ITEMCODE, A.ITEMNAME, A.STOCKINHAND, B.SALEPRICE FROM ITEMMASTER A, STOCKENTRY B WHERE A.ITEMID = B.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID) AND A.STOCKINHAND > 0 AND B.SALEPRICE > 0 AND B.INVOICEDATE IS NOT NULL ORDER BY A.ITEMNAME, B.INVOICEDATE;
# Time: 110805 13:31:21
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.031250  Lock_time: 0.000000 Rows_sent: 184  Rows_examined: 2898
SET timestamp=1312531281;
SELECT B.RECORDID, A.ITEMCODE, A.ITEMNAME, A.STOCKINHAND, B.SALEPRICE FROM ITEMMASTER A, STOCKENTRY B WHERE A.ITEMID = B.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID) AND A.STOCKINHAND > 0 AND B.SALEPRICE > 0 AND B.INVOICEDATE IS NOT NULL AND A.ITEMNAME LIKE '%';
# Time: 110805 13:36:31
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.250000  Lock_time: 0.000000 Rows_sent: 407  Rows_examined: 20367
SET timestamp=1312531591;
select c.categoryname, 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, D.TradeName, D.OwnerAddress, D.OwnerTinNo from itemmaster a, stockentry b, categorymaster c, OwnerDetails D where a.itemid = b.itemid and a.categoryid = c.categoryid and invoicedate = (select max(invoicedate) from stockentry where itemid = b.itemid) group by a.categoryid, a.itemname order by a.itemname;
share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

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.

share|improve this answer
    
I notice Slow Query logs still filled even after changing queries. –  RPK Aug 6 '11 at 16:55
add comment

First off, most of these seem to be 'slow' because they're not using indexes. You need to analyze how you're accessing the data in your tables to determine proper indexes. But a few pointers from this file:

SELECT * FROM REMARKSETTINGS;

and

SELECT * FROM BILLINGSETTINGS;

These currently only have 1 row, but if they ever grow, you're asking for slowdown. A couple things you can do here is add a WHERE clause (I can't make a recommendation without knowing the DDL) or at the very least only select the columns you need instead of '*'.

For

SELECT B.RECORDID, A.ITEMCODE, A.ITEMNAME, A.STOCKINHAND, B.SALEPRICE 
FROM ITEMMASTER A, STOCKENTRY B 
WHERE A.ITEMID = B.ITEMID 
  AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY 
                  WHERE ITEMID = A.ITEMID) 
  AND A.STOCKINHAND > 0 
  AND B.SALEPRICE > 0 
  AND B.INVOICEDATE IS NOT NULL 
ORDER BY A.ITEMNAME, B.INVOICEDATE;

From first glance, make sure you have an index on recordID.

May I make a suggestion and read up on indexing? Indexing is a great help, but if you over-index, it's as bad as under-indexing.

share|improve this answer
    
The tables, BillingSettings and RemarkSettings, will not ever contain more than one row. For the last query, I do have an index on RecordID. This is the link of the indexes I have: postimage.org/image/2k015uuck –  RPK Aug 5 '11 at 14:33
    
@RPK try adding EXPLAIN before that query to see how mysql parses it. –  Derek Downey Aug 5 '11 at 14:40
add comment

A variation of @Mark Storey-Smith's solution is to store specific date information in a calendar table. This table may contain as fields: * day field for all affected calendar dates, ie. 1990-01-01 to 2020-12-31. This field will be joined

  • year number field
  • month number field
  • day in month
  • day of week
  • week in month (there are several definitions)
  • public holiday

Depending on query, you need to index all of these columns (and maybe have some multiple column ones)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.