MySQL

Help optimizing a query

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Feb 2008

Posts: 6

Reputation: bernardf is an unknown quantity at this point

Solved Threads: 0

bernardf

Offline

Newbie Poster

Help optimizing a query

Dec 10th, 2008

Hi there,

Can someone please help me in optimizing a query.

The query that I have is as follows:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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
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

•

Join Date: Nov 2008

Posts: 823

Reputation: verruckt24 is a jewel in the rough

Solved Threads: 73

verruckt24 verruckt24 is offline

Offline

Practically a Posting Shark

Re: Help optimizing a query

Dec 10th, 2008

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.

Get up every morning and take a look at the Forbes' list of richest people. If your name doesn't appear.... GET TO WORK !!!