Thanks Robert;
Shortly after I posted my message, the optimizer came back to realization of
using the index, and the performance of the application went back to normal.
I think you're right about "Rowno <= 1"; the optimizer is probably not
taking it into account. When the index kicked back in, the AVAILABLE
column still had 'Y' in about 99% out of the 100.000 tickets sharing the
same L_ID.
In the application the L_ID is actually placed first in the condition part
of the statement. Using the in-line view, gives worse result, and
according to the optimizer, costs considerably more. I totally agree with
you, using '*' should generally be avoided.
Kind regards,
Helgi Örn Viggósson,
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]
