Re: The Optimizer?

From: "Pons Roca, Isidre" <ipons@dtgna.altanet.org> > >This a very curious situation: > > take a table > > table_a ( > column_1 char(3) not null, > column_2 char(2) not null, > column_3 smallint not null, > column_4 integer, > ... > colum_n smallint, > primary key (column_1,column_2,column_3)) > > Put into this table 5,500,000 records (or more ;-DDDD) > > execute the next sql statment: > > select * > from table_a > where column_2 = "xx" and > column_2 = "yy"> > ***** This select is wrong because a column only could have a >value in a record. > > Time expended to say "No records found" was 30 minutes with >a lot of reads. > > Now replace the column_2 by column_1 in the where clausule > (where column_1 = "xxx" and column_1 = "yyy") > > The time expended with this select = 0.00000001 miliseconds >and 3 reads to say "No records found". > >Why the optimizer don't optimize the first select? Because it's not indexed. >Why the optimizer optimize the second select (because column_1 >it's the first item of a index?) ? Yep. Have you run UPDATE STATISTICS? ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com