I worked up some additional variations on creating the same result set.
Would you mind giving them a try to see if any one of them works better
with your indexes?
FROM dimension
INNER JOIN factable
ON dimension.id = factable.id
AND factable.Date >= '2004-06-15'
AND factable.Date < '2004-06-22'
- or -
FROM dimension
INNER JOIN factable
ON dimension.id = factable.id
AND factable.Date >= '2004-06-15'
WHERE factable.Date < '2004-06-22'
- or -
FROM dimension
INNER JOIN factable
ON dimension.id = factable.id
WHERE factable.Date >= '2004-06-15'
AND factable.Date < '2004-06-22'
- or -
FROM dimension
INNER JOIN factable
ON dimension.id = factable.id
WHERE factable.Date BETWEEN '2004-06-15' and '2004-06-22'
AND factable.Date < '2004-06-22'
- or -
FROM dimension
INNER JOIN factable
ON dimension.id = factable.id
AND factable.Date BETWEEN '2004-06-15' and '2004-06-22'
WHERE factable.Date < '2004-06-22'
- or -
FROM dimension
INNER JOIN factable
ON dimension.id = factable.id
AND factable.Date < '2004-06-22'
WHERE factable.Date BETWEEN '2004-06-15' and '2004-06-22'
(These last three use the BETWEEN clause (which is returns a closed set)
and opens one end by excluding the higher match. I am hoping that the
BETWEEN will pick up and use the second key in your index where the other
statements may not have.)
Sorry I couldn't offer any concrete advice.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"David Perron"
<[EMAIL PROTECTED] To: <[EMAIL PROTECTED]>
om> cc: <[EMAIL PROTECTED]>
Fax to:
06/22/2004 03:38 Subject: RE: Using date ranges on a
composite key degrades performance
PM
Ive tried both ways.
The first way uses:
INNER JOIN dimension USING (id)
INNER JOIN fact_table USING (id)
WHERE factable.Date >= '2004-06-15'
AND factable.Date < '2004-06-22'
The second way:
dimension STRAIGHT_JOIN fact_table
WHERE factable.Date >= '2004-06-15'
AND factable.Date < '2004-06-22'
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 22, 2004 3:16 PM
To: David Perron
Cc: [EMAIL PROTECTED]
Subject: Re: Using date ranges on a composite key degrades performance
Are you using the JOIN ... ON... syntax or the "comma" syntax of joining
tables?
"David Perron"
<[EMAIL PROTECTED] To:
<[EMAIL PROTECTED]>
om> cc:
Fax to:
06/22/2004 02:46 Subject: Using date ranges
on a composite key degrades performance
PM
Im finding that when I execute a query joining to a large fact table (~450
million rows) using a composite key of INT(11) & DATE for a single Date
i.e.
AND dimension.id = factable.id
AND factable.Date = '2004-06-22'
the query returns exceptionally fast. However, when I change this to a
range, i.e.
AND dimension.id = factable.id
AND factable.Date >= '2004-06-15'
AND factable.Date < '2004-06-22'
the performance degrades disproportionately to the number of single day
queries it would take to get the results.
Since the main part of the SELECT statement is an aggregate, the first
query
does me no good.
Does anyone have a similar experience, and/or suggestion for improving the
performance of this?
--
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
