Re: Query Optimizer bug!?
am 22.07.2002 21:26:13 von spetersI 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
CREATE TABLE property (
Property_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Unique_Taxkey CHAR(25),
Process_Match_Taxkey CHAR(25),
Search_Taxkey CHAR(25),
Display_Taxkey CHAR(25),
County_ID CHAR(2) NOT NULL,
Municipality_ID CHAR(3) NOT NULL,
Display_Num_Low CHAR(10),
Display_Num_High CHAR(10),
Coord_Dir_1 CHAR(1),
Coord_Dir_2 CHAR(1),
Coord_Num_Low INT NOT NULL DEFAULT 0,
Coord_Num_High INT NOT NULL DEFAULT 0,
Street_Num_Low INT NOT NULL DEFAULT 0,
Street_Num_High INT NOT NULL DEFAULT 0,
Direction CHAR(2),
Street_Name CHAR(25),
Building_Number CHAR(10),
Unit_Number CHAR(10),
State CHAR(2),
Zip_Code CHAR(10),
Owner_Name_1 CHAR(32),
Owner_Name_2 CHAR(32),
Label_Name_1 CHAR(32),
Label_Name_2 CHAR(32),
Owner_Address CHAR(32),
Owner_City CHAR(25),
Owner_State CHAR(2),
Owner_Zip_Code CHAR(10),
Zoning CHAR(7),
Land_Use CHAR(4),
Census_Tract DECIMAL(6,2),
School_District CHAR(4),
Township CHAR(3),
Range CHAR(3),
Quarter CHAR(3),
Section CHAR(3),
Building_Type CHAR(4),
Number_Of_Stories DECIMAL(5,2),
Number_Of_Units INT(4),
Building_Square_Feet INT,
Basement CHAR(2),
Attic CHAR(2),
Pool CHAR(1),
Fireplace CHAR(1),
Air_Conditioning CHAR(1),
Heat CHAR(2),
Total_Rooms INT,
Bedrooms INT,
Full_Baths INT,
Half_Baths INT,
Garage CHAR(2),
Exterior_Wall CHAR(2),
Exterior_Condition CHAR(2),
Year_Built INT(4),
Year_Remodeled INT(4),
Effective_Year_Built INT(4),
Historic_Designation CHAR(2),
Assessment_Year INT NOT NULL,
Land_Total INT,
Improvement_Total INT,
Assessment_Total INT,
Acres_Total DECIMAL(15,3),
Tax_Year INT NOT NULL,
Tax_Before_Lottery_Credit DECIMAL(12,2),
Lottery_Credit DECIMAL(12,2),
Tax_After_Lottery_Credit DECIMAL(12,2),
Special_Taxes DECIMAL(12,2),
Special_Assessment DECIMAL(12,2),
Special_Charges DECIMAL(12,2),
Full_Pay_Amount DECIMAL(12,2),
Create_Time DATETIME,
Modify_Time TIMESTAMP,
UNIQUE KEY (Unique_Taxkey),
UNIQUE KEY process_match_taxkey(County_ID,
Municipality_ID,Process_Match_Taxkey),
UNIQUE KEY search_taxkey(County_ID, Municipality_ID, Search_Taxkey),
KEY coord_dir_1(County_ID, Municipality_ID, Coord_Dir_1),
KEY coord_dir_2(County_ID, Municipality_ID, Coord_Dir_2),
KEY coord_num_high(County_ID, Municipality_ID, Coord_Num_Low),
KEY coord_num_low(County_ID, Municipality_ID, Coord_Num_High),
KEY street_num_low(County_ID, Municipality_ID, Street_Num_Low),
KEY street_num_high(County_ID, Municipality_ID, Street_Num_High),
KEY street_name(County_ID, Municipality_ID, Street_Name(12) ),
KEY zip_code(County_ID, Municipality_ID, Zip_Code),
KEY owner_name_1(County_ID, Municipality_ID, Owner_Name_1(12) ),
KEY owner_name_2(County_ID, Municipality_ID, Owner_Name_2(12) ),
KEY owner_zip_code(County_ID, Municipality_ID, Owner_Zip_Code),
KEY Zone_Search (County_ID, Municipality_ID, Zoning),
KEY Land_Use_Search (County_ID, Municipality_ID, Land_Use),
KEY school_district(County_ID, Municipality_ID, School_District),
KEY building_type(County_ID, Municipality_ID, Building_Type),
KEY number_of_units(County_ID, Municipality_ID, Number_Of_Units),
KEY number_of_stories(County_ID, Municipality_ID, Number_Of_Stories),
KEY building_square_feet(County_ID, Municipality_ID, Building_Square_Feet),
KEY basement(County_ID, Municipality_ID, Basement),
KEY attic(County_ID, Municipality_ID, Attic),
KEY heat(County_ID, Municipality_ID, Heat),
KEY total_rooms(County_ID, Municipality_ID, Total_Rooms),
KEY bedrooms(County_ID, Municipality_ID, Bedrooms),
KEY full_baths(County_ID, Municipality_ID, Full_Baths),
KEY half_baths(County_ID, Municipality_ID, Half_Baths),
KEY garage(County_ID, Municipality_ID, Garage),
KEY exterior_wall(County_ID, Municipality_ID, Exterior_Wall),
KEY exterior_condition(County_ID, Municipality_ID, Exterior_Condition)
);
------------------------------------------------------------ ---------
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-thread12209@lists.mysql.com
To unsubscribe, e-mail