Re: Query Optimizer bug!?

speters@metromls.com writes:
> I have just discovered what i can only classify as a MySQL bug with the
> query optimizer.
>
> I am running MySQL version 4.0.1-alpha (which may be outdated)
> on Solaris 2.7
>
> The problem is with MyISAM tables.
> If i use EXACTLY 32 indexes, (the max for MyISAM as i have them configured)
> then the query optimizer doesn't properly choose indexes when executing
> select queries.
> This may only be a problem with multi-column indexes, as I have mostly
> indexes that whose leftmost columns are the same.
> I include the table creation statement at the end of this message.
>
> I disconvered this problem when issuing queries that should have executed
> quickly took quite a while.
> I did a number of explain statements on queries, and convinced myself that
> the indexes weren't being used properly.
> Then, I tried re-building the table with less indexes. The tables with less
> indexes had no problem executing queries as I expected them to be optimized.
> This even worked when i had 31 indexes, but not with 32.
>
> How-To-Repeat:
> have a version of MySQL configured for max 32 keys for MyISAM tables.
> 1) create the table as i specify below.
> 2) put some data in it.
> 3) try a query like (depending on how you populate the table)
> EXPLAIN
> SELECT count(*) from property
> WHERE County_ID='67' AND Municipality_ID='132' AND Owner_Name_1 LIKE
> 'Smith%';
>
> - The explanation of this query should not actually use any indexes.
>
> 4) drop ANY index except the one we're trying to use in the query.
> 5) try the query again. This time, it should use the index.
>
>
> If you need any more info, please let me know. MySQL is a great DB server,
> and I want to help make it better.
>
> regards,
> Sean Peters
> Senior Programmer, WireData Inc.
> speters@wiredata.com
>

Hi!

Can you please try the aobve with 4.0.2 as I have checked and keys are
now mapped to an 8 bit integer, which means that the above limitation
should have been removed.

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12239@lists.mysql.com
To unsubscribe, e-mail