Member Avatar for bernardf

Hi there,

Can someone please help me in optimizing a query.

The query that I have is as follows:

CREATE TAbLE RED_WINE_TXN AS
SELECT MEMBER_SEQUENCE, SUM(PURCHASE_VALUE) PURCHASE_VALUE, SUM(QUANTITY) QTY
FROM PURCHASE_DETAILS a, RED_WINE_SKUS b
WHERE a.PRODUCT_NO=b.PRODUCT_NO
AND TRANSACTION_DATE>=20071101
GROUP BY MEMBER_SEQUENCE

MEMBER_SEQUENCE: DECIMAL (INDEX)
PURCHASE_VALUE: DECIMAL
QUANTITY: DECIMAL
PRODUCT_NO: DECIMAL (INDEX ON BOTH TABLES)
TRANSACTION_DATE: DATETIME

If anyone knows what steps I should take to speed this up that would be great.

Thanks, Bernie

  • 2 Contributors
  • 1 Reply
  • 55 Views
  • 13 Hours Discussion Span
  • Latest Post Latest Post by verruckt24
Member Avatar for verruckt24

Use EXPLAIN followed by your query to get an explanation about the query from MySQL. Then you can analyze this output to know how your query is performing, then based on this you can optimize your query accordingly.

Suggestion : Use the Internet to your advantage, you could have as easily found this out from the net if you would have taken the pains to search the net.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.