Optimizing Query. Want to pick the last record without using max in sub-query

This is my query:

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;

Table B (StockEntry) may contain one or more records whereas Table A (ItemMaster) will definitely have only a single row for that ItemID.

If I remove the sub-query in the WHERE clause, it displays one or more rows. I feel that picking max(RecordID) through sub-query in the WHERE clause is slowing query. I do have indexes on RecordID, InvoiceDate, ItemID but still MySQL logs show that this query is not performing well. I can't change the column sequence for some reason.

Is there any better way to optimize this query?

It's likely slow because it's running a correlated subquery for every row of the outer query. There are two solutions that tend to run more efficiently.

One is to use a derived table, which uses a subquery, but it only executes the subquery once to prepare the derived table.

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

The other solution is to use an exclusion join to find the row in B such that no other row exists with the same itemid and a greater recordid. With correct indexes (e.g. a compound index on (ITEMID, RECORDID), this should perform very well.

SELECT B.RECORDID, A.ITEMCODE, A.ITEMNAME, A.STOCKINHAND, B.SALEPRICE 
FROM ITEMMASTER A
JOIN STOCKENTRY B ON A.ITEMID = B.ITEMID 
LEFT OUTER JOIN STOCKENTRY B2
  ON B.ITEMID = B2.ITEMID AND B.RECORDID < B2.RECORDID
WHERE B2.ITEMID IS NULL 
  AND A.STOCKINHAND > 0 
  AND B.SALEPRICE > 0 
  AND B.INVOICEDATE IS NOT NULL 
ORDER BY A.ITEMNAME, B.INVOICEDATE;

This type of problem comes up frequently on Stack Overflow. I've added the greatest-n-per-group tag to the question so you can see other cases.

Re @RPK's comment:

I don't use MySQL QB myself, and that app has changed so many times I can't advise on how to use it. But in the mysql monitor (command-line), I use a combination of EXPLAIN and PROFILING to give me stats.

However, you made a comment about not being to modify (or create?) indexes. That's going to hamstring your attempts to optimize.

try using TOP 1 ... ORDER BY .. DESC, like this:

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

TOP is database-specific; you may want to use the MySQL alternative ORDER BY ... DESC LIMIT 1.

This SO post has a good overview of the different ways to implement LIMIT concept across databases.

My suggestion is create a view

CREATE VIEW `STOCKENTRY_V` AS 
SELECT ITEMID,MAX(RECORDID) AS RECORDID
FROM STOCKENTRY
GROUP BY ITEMID;

And you can do a simple join on 2 tables + the view. I'm interested how fast it will perform.

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

If the query is frequently used and performance is still an issue, you could create a table of the last record id for an item and keep it up to date using a trigger on the ITEMMASTER table.