Re: Query Optimizer bug!?

Hi!

Sorry for the delayed responce, but I have been on vacation...

>>>>> "speters" == speters writes:

speters> I have just discovered what i can only classify as a MySQL bug with the
speters> query optimizer.



speters> How-To-Repeat:
speters> have a version of MySQL configured for max 32 keys for MyISAM tables.
speters> 1) create the table as i specify below.
speters> 2) put some data in it.
speters> 3) try a query like (depending on how you populate the table)
speters> EXPLAIN
speters> SELECT count(*) from property
speters> WHERE County_ID='67' AND Municipality_ID='132' AND Owner_Name_1 LIKE
speters> 'Smith%';

speters> - The explanation of this query should not actually use any indexes.

I tried this on the upcoming MySQL 4.0.3 version, but this worked
without any problems for me:

12','131','test'),('67','132','Smith'),('69','133','Smith2') ,('67','132','valruss');
Query OK, 4 rows affected (0.11 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> EXPLAIN
-> SELECT count(*) from property
-> WHERE County_ID='67' AND Municipality_ID='132' AND Owner_Name_1 LIKE
-> 'Smith%';
+----------+------+----------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ---------------------------------------------------------+-- -------------+---------+-------------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------+------+----------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ---------------------------------------------------------+-- -------------+---------+-------------+------+------------+
| property | ref | search_taxkey,process_match_taxkey,coord_dir_1,coord_dir_2,c oord_num_high,coord_num_low,street_num_low,street_num_high,s treet_name,zip_code,owner_name_1,owner_name_2,owner_zip_code ,Zone_Search,Land_Use_Search,school_district,building_type,n umber_of_units,number_of_stories,building_square_feet,baseme nt,attic,heat,total_rooms,bedrooms,full_baths,half_baths,gar age,exterior_wall,exterior_condition | search_taxkey | 5 | const,const | 1 | where used |
+----------+------+----------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ---------------------------------------------------------+-- -------------+---------+-------------+------+------------+
1 row in set (0.04 sec)

To be able to help you, we would need a full test case, including
data, that shows how to repeat this problem.

Regards,
Monty

--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ 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-thread12285@lists.mysql.com
To unsubscribe, e-mail