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]
