problem with mysqldump when there is a merge table

[nyem]

Hello,

I have a database (on Win2k) with merge table. Mysqldump output includes 
some path information on the merged tables such as DATA DIRECTORY and 
INDEX DIRECTORY


DROP TABLE IF EXISTS `rptpricing1996`;
CREATE TABLE `rptpricing1996` (
 `PricingId` int(11) NOT NULL default '0',
 `commodity` char(22) NOT NULL default '',
 `variables` char(7) NOT NULL default '',
 `PricingDt` date NOT NULL default '-00-00',
 `PricingHighPrice` decimal(12,2) default NULL,
 `PricingLowPrice` decimal(12,2) default NULL,
 `PricingAvgPrice` decimal(12,2) default NULL,
 PRIMARY KEY  (`PricingId`),
 KEY `commodityDate` (`commodity`,`PricingDt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DATA DIRECTORY='C:\SDVI\DB\mysql\data\dbsdvi\'
INDEX DIRECTORY='C:\SDVI\DB\mysql\data\dbsdvi\';


When trying to load the database back (mysql dbsdvi < dbsdvi.dump)
the path information produces errors as mysql treats the \ character as 
an escaped character.


Removing the path information eliminates the error, but how do I tell 
mysqldump not to output the path information?


regards,
nyem






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: slow insert into select statement

[nyem]

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.PricingDtas PrevDate,
   a.PricingAvgPrice  as PrevAvg,
   b.PricingDtas 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]

slow insert into select statement

[nyem]

I have this insert statement that took a long time to execute:
INSERT INTO priceLast5
SELECT
   DISTINCT a.commodity,
   a.PricingDt
FROM rptPricingTEST a
WHERE
   a.PricingDt = (
 SELECT MAX(PricingDt)
 FROM rptPricingTEST b
 WHERE
   b.PricingDt > @date1
   AND b.PricingDt <= @date2
   AND a.commodity = b.commodity
   );
> Query OK, 3010 rows affected (4 min 17.37 sec)
Is there any way I can modify the sql statement to speed this up?
Here's the result of explain (splitted for easy reading):
id select_typetable type  possible_keys key
-- -- - - - 
 1 PRIMARYa index NULL  idxcommodity
 2 DEPENDENT SUBQUERY b ref   idxcommodity  idxcommodity
id key_len ref   rows   Extra
-- --- - -- 
 1  25 NULL  384155 Using where; Using index
 2  22 dbsdvitmp.a.commodity 52 Using where; Using index

And here's the rest of the sql before the above insert:
SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH);
SELECT @date2:=CURDATE();

CREATE TEMPORARY TABLE priceLast5 (
commodity char(22) NOT NULL,
PricingDt date NOT NULL,
PRIMARY KEY  (commodity),
KEY idxPricingDt (PricingDt),
);

LOCK TABLES rptPricingTEST a READ;
The source table rptPricingTEST consists of commodity id (which is a 
concat of several ids), pricing date, and prices (low, hi, avg). I need 
to generate a report of price change for all commodity for the date 
interval selected by users. Since this will be a web-based report, to 
wait for 4 minutes for a response is unacceptable. The actual table has 
2 million rows which I guess can take forever to execute.

regards,
nyem
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

sloooow sql query

[nyem]

Hello,

I have this table which stores an ever changing price of items.

CREATE TABLE shop (
  article   INT(4) UNSIGNED ZEROFILL NOT NULL default '',
  date  DATE NOT NULL default '-00-00',
  price DOUBLE(16,2) NOT NULL default '0.00',
  KEY article (article,date)
);
And I used this query to get the percentage change in price of the items:

SELECT
s1.article,
s2.date as prevDate, s2.price as prevPrice,
s1.date as lastDate, s1.price as lastPrice,
ROUND(((s1.price-s2.price)/s2.price)*100, 1) as percentChange
FROM shop s1, shop s2
WHERE
s1.date=(
SELECT MAX(s3.date) FROM shop s3
WHERE s1.article=s3.article
GROUP BY article
) AND
s2.date=(
SELECT MAX(s4.date) FROM shop s4
WHERE s1.article=s4.article AND s4.date
It works fine on small number of rows, but when the table reaches 400 rows 
the time it took to execute the query was 16 sec. And my cpu shot up to 
100% whenever I populate 1000 rows. What have I done wrong here?

regards,
nyem



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]