Something I have done in the past (with another DB system) was to put indexes on my temp tables. I have just gone over my copy of the MySQL docs and I don't see where that is NOT allowed so I think its fair game to try it.
You have two options to do this: Create the temp tables then populate them (two statements) -or- Use ALTER TABLE your temp tables to create the indexes. I have no data about which is definitely faster although I have read many times that if you index a table AFTER filling it with data, the entire operation tends to finish quicker (especially if you have lots of data). Since you are using multiple values in your joins (when you are collecting the historical prices) lets try indexing tmpLatestDates and tmpPrevDates on both columns. Do you have an index on rptPricingTest for (commodity, PricingDt) ? That could also speed up the joins. You could also combine the collection of one set of data (I chose to do your previous months values)with your final results calculations. Maybe this will work faster.... SELECT @start:=NOW(); SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH); SELECT @date2:=CURDATE(); CREATE TEMPORARY TABLE tmpLatestDates SELECT commodity, MAX(PricingDt) as MaxDate FROM rptPricingTEST WHERE PricingDt > @date1 AND PricingDt <= @date2 GROUP BY commodity; ALTER TABLE tmpLatestDates ADD KEY (commodity, MaxDate); CREATE TEMPORARY TABLE tmpLatestPrices SELECT b.commodity, a.PricingDt, a.PricingHighPrice, a.PricingLowPrice, a.PricingAvgPrice FROM rptPricingTEST a INNER JOIN tmpLatestDates b ON b.commodity = a.commodity AND b.MaxDate = a.PricingDt; ALTER TABLE tmpLatestPrices ADD KEY(commodity); CREATE TEMPORARY TABLE tmpPrevDates SELECT a.commodity, MAX(a.PricingDt) as PrevDate FROM rptPricingTEST a, tmpLatestPrices b WHERE a.PricingDt < @date1 AND a.commodity = b.commodity GROUP BY commodity; ALTER TABLE tmpPrevDates ADD KEY(commodity, PrevDate); /* I eliminated one temp table and added some indexes */ SELECT a.commodity, a.PricingDt as PrevDate, a.PricingAvgPrice as PrevAvg, tlp.PricingDt as LatestDate, tlp.PricingAvgPrice as LatestAvg, ((tlp.PricingAvgPrice - a.PricingAvgPrice) /a.PricingAvgPrice) * 100 as priceChange FROM rptPricingTEST a INNER JOIN tmpPrevDates tpd ON a.commodity = tpd.commodity AND a.PricingDt = tpd.PrevDate INNER JOIN tmpLatestPrices tlp ON tlp.commodity = a.commodity; DROP TABLE IF EXISTS tmpLatestDates, tmpPrevDates, tmpLatestPrices SELECT TIMEDIFF(NOW(),@start); Now, because we are using INNER JOINS, only those commodities that actually existed in the previous month will be returned. That actually makes some sense as you cannot calculate a % change from "nonexistence" to "some value". Let us know how this works out, OK? Shawn Green Database Administrator Unimin Corporation - Spruce Pine nyem <[EMAIL PROTECTED] To: [EMAIL PROTECTED] .my> cc: Fax to: 05/27/2004 05:01 Subject: Re: slow insert into select statement AM Thanks for all the feedback. Here's my latest attempt: SELECT @start:=NOW(); SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH); SELECT @date2:=CURDATE(); LOCK TABLES rptPricingTEST READ, rptPricingTEST a READ; CREATE TEMPORARY TABLE tmpLatestDates SELECT commodity, MAX(PricingDt) as MaxDate FROM rptPricingTEST WHERE PricingDt > @date1 AND PricingDt <= @date2 GROUP BY commodity; CREATE TEMPORARY TABLE tmpLatestPrices SELECT b.commodity, a.PricingDt, a.PricingHighPrice, a.PricingLowPrice, a.PricingAvgPrice FROM rptPricingTEST a INNER JOIN tmpLatestDates b ON b.commodity = a.commodity AND b.MaxDate = a.PricingDt; CREATE TEMPORARY TABLE tmpPrevDates SELECT a.commodity, MAX(a.PricingDt) as PrevDate FROM rptPricingTEST a, tmpLatestPrices b WHERE a.PricingDt < @date1 AND a.commodity = b.commodity GROUP BY commodity; CREATE TEMPORARY TABLE tmpPrevPrices SELECT a.commodity, a.PricingDt, a.PricingHighPrice, a.PricingLowPrice, a.PricingAvgPrice FROM rptPricingTEST a INNER JOIN tmpPrevDates b ON b.commodity = a.commodity AND b.PrevDate = a.PricingDt; SELECT a.commodity, a.PricingDt as PrevDate, a.PricingAvgPrice as PrevAvg, b.PricingDt as LatestDate, b.PricingAvgPrice as LatestAvg, ((b.PricingAvgPrice - a.PricingAvgPrice) /a.PricingAvgPrice) * 100 as priceChange FROM tmpPrevPrices a, tmpLatestPrices b WHERE a.commodity = b.commodity AND a.PricingAvgPrice < b.PricingAvgPrice; UNLOCK TABLES; DROP TABLE IF EXISTS tmpLatestDates, tmpPrevDates, tmpLatestPrices, tmpPrevPrices; SELECT TIMEDIFF(NOW(),@start); +------------------------+ | TIMEDIFF(NOW(),@start) | +------------------------+ | 00:00:08 | +------------------------+ 1 row in set (0.00 sec) Now I get it all executed in 8 seconds. [EMAIL PROTECTED] wrote: > .. snip > > After reviewing your original post, I am not confident that you are > answering your question with this query. You said "I need to generate a > report of price change for all commodity for the date interval selected by > users." which to me implies a report something like: > > +---------+-------------+-----------+--------------+------------+---------+---------+---------+ > |commodity|starting date|ending date|starting price|ending price|max > price|min price|avg price| > +---------+-------------+-----------+--------------+------------+---------+---------+---------+ > > I am just not sure where you are going with the query you are building. Can > you provide a template of the results you want? > > Thanks, > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine You were right about this. I was merely posting the first part of my query, as that's where my trouble began. Here's the result table that I'm looking for, where priceChange is the % increase in price for the two date interval. +-----------+----------+---------+------------+-----------+-------------+ | commodity | PrevDate | PrevAvg | LatestDate | LatestAvg | priceChange +----------------------+---------+------------+-----------+-------------+ The slowest part of the script is when generating this table (5.00 sec). How could I optimise it further? I don't mind creating more temp tables as long as the total execution time is much reduced. I'm using mysql 4.1.1a-alpha on win2k. Thanks, nyem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]