2005/12/17, Helgi Viggosson <[EMAIL PROTECTED]>:
> Hello all;
>
>
>
> I'm using 7.6.00.09 on Windows XP, and I've a situation where suddenly the
> optimizer goes nuts and ceases using an [obvious] index resulting in severe
> performance slump.
>
>
>
> The index is defined as following:
>
>
>
> CREATE INDEX "AVAILIDX" ON "FDB"."TICKETS"("L_ID" ASC, "AVAILABLE" DESC)
>
>
>
> And at the moment there are 100.000 rows in the table, and the column
> "AVAILABLE" is predominantly set to 'Y'. The other possible value is 'N'.
> When querying the optimizer this is the conclusion:
>
>
>
> explain select * from tickets where available='Y' and l_id =
> '55039423590512150001' and Rowno <= 1 // this is the query the
> application uses
>
>
>
> Gives a table scan and cost of 1603 while
>
>
>
> explain select * from tickets where available='N' and l_id =
> '55039423590512150001' and Rowno <= 1
>
>
>
> Gives an index scan and cost of 16
>
>
>
> I've tried to execute the query in multiple versions, and saving the
> optimizer statistics in-between, rebuild the index, etc., but to no luck
> (this worked for me on the same db few months ago). Is there any means to
> force the optimizer to use the index?
I'm not sure it would be wise. Since 'Y' is the predominant value in
the table the optimizer probably decides to do a full table scan
because normally that would be cheaper when looking for the
predominant value. It probably doesn't take into account the "rowno <=
1" part. Things I'd try in this situation:
- reorder query conditions: where l_id =
'55039423590512150001' and available='Y' and Rowno <= 1
- use an inline view
select *
from (
select * from tickets where l_id =
'55039423590512150001' ) X
X.available='Y' and Rowno <= 1
And a general remark: avoid using "select *". It makes your code
vulnerable by column reorderings and additions. Also, it's better
documentation wise and you might not actually need all columns. Dunno
whether it also has an impact on optimizer decisions.
Kind regards
robert
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
