toDate fromDate 1992-03-02 1991-01-23 1992-04-03 1990-06-13 1993-05-03 1991-01-22 ... ... ... 2004-12-01 2003-02-22 2005-03-04 2003-02-22 (a) 2005-03-05 2004-12-15 (b) 2005-03-05 2004-06-18 (c) 2007-04-12 2005-06-18 (d)
Let's say that there are a million entries where the toDate is less than today 2004-12-02. That is less than (a) in the index. From the index then only a, b, c, and d should be scanned further. a and c would be picked based on the index values because 2004-12-02 is between the from and end date. However, b and d would be excluded immediately because the the from date is greater than 2004-12-02 and would save the optimizer from even reading the table for these index entries because the fromDate is in the index.
This may be a somewhat extreme example but my experience is in most systems old historical data makes up the bulk of the data and newer data is a much smaller amount. In addition most people are interested in data from the most recent month.
Of course I may be mistaken about the data distribution.
Vincent
----- Original Message ----- From: "Bruno Wolff III" <[EMAIL PROTECTED]>
To: "Vincent Hikida" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, December 02, 2004 12:10 PM
Subject: Re: [GENERAL] Indexes?
On Wed, Dec 01, 2004 at 23:16:48 -0800, Vincent Hikida <[EMAIL PROTECTED]> wrote:I believe that it is better to have a concatenated key of
(toDate,FromDate). The reason the toDate should come first is that for more
"recent" records, finding curDates less than toDate is much more selective
than finding curDates greater than fromDate. Actually I'm not sure if
fromDate is that helpful either as part of the concatenated key (it
probably depends) but definitely not by itself.
I combined index won't be very useful for the kind of search he is doing. And not having an index on FromDate could hurt in some cases depending on the distribution of values.
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
