[Fwd: query performance]

This is a multi-part message in MIME format. --------------27DEDA0AECB35305C8758837 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit problems to re-transmit message, forward will be ok , --------------27DEDA0AECB35305C8758837 Content-Type: message/rfc822 Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-ID: <363619EF.48856D91@worldonline.nl> Date: Tue, 27 Oct 1998 19:07:28 +0000 From: "Henk J. Sanders" <hjs@worldonline.nl> X-Mailer: Mozilla 4.05 [en] (Win95; I) MIME-Version: 1.0 To: Billy Wheeler <billy@west.co.za> Subject: Re: query performance References: <199810270602.IAA21524@west.co.za> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit output from explain: QUERY: --------- select count(*) from rvr where fst_date <= "1998-4-1" and lst_date >= "1998-4-1" Estimated Cost: 10304 Estimated # of Rows Returned:1 1) dtv.rvr: INDEX PATH Filters: dtv.rvr.fst_date <= 1998-04-01 (1) Index Keys: lst_date rvr_uniq Lower Index filter: dtv.rvr.lst_date >- 1998-04-01 Remarks: 1. count = 852 2. rvr_uniq is a serial added to avoid the problems with duplicate entries Kind regards. Billy Wheeler wrote: > On 26 Oct 98, at 18:44, Henk J. Sanders wrote: > > > Some people have already reacted, many thanks. > > > > To give some more information: > > > > SQLEXPLAIN shows that the index lst_date is taken. > > Can you post the contents of sqexplain.out? > > > All other queries perform excellent, only this one is in trouble. > > > > To whom we do not really understand the problem: > > > > fst_date <= "1998-4-1" ...... > > LST_date >= "1998-4-1" > > > > Example (by fst_date index) > > > > fst_date lst_date > > ---------- ---------- > > 1998-3-30 1998-4-1 > > 1998-3-30 1998-4-2 > > 1998-3-30 1998-4-3 > > .... > > ..... > > 1999-4-1 > > 1998-4-1 1998-4-1 > > 1998-4-1 1998-4-2 > > 1998-4-1 1998-4-2 > > ..... > > ...... > > 1999-4-1 > > 1998-4-2 > > and so on. > > > > As you will see that an index will not be of much help. > > The column lst_date was added to the index to avoid lots of duplicate > > index entries: about 200 a day. > > > > If more information is required please let me know. > > > > > > > > -- > > Ciao, > Billy > > /Group Managing Director, The West Solutions Group: http://www.west.co.za > \\ Drivel @ http://www.west.co.za/tasteless/ > / > \\ "Granted, Mr Wheeler's ideas are stupid and unreasonable, but he does own the company and I > / think we should go along with him..." > \\________________________________________________________________________________________________ --------------27DEDA0AECB35305C8758837--