Comments - index pushdown - Bug or Side Effect

Hi,

Could you please provide the structure of the tables `humans` and `towncities` (the output of SHOW CREATE TABLE)?

Thank you.

CREATE TABLE `humans` ( `human_id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(24) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '', `first_name_soundex` varchar(4) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, `last_name` varchar(24) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '', `last_name_soundex` varchar(4) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, `birth_name` varchar(64) NOT NULL DEFAULT '', `company_name` varchar(30) DEFAULT NULL, `dob` date NOT NULL DEFAULT '0000-00-00', `tel` varchar(32) DEFAULT NULL, `fax` varchar(32) DEFAULT NULL, `email` varchar(64) DEFAULT NULL, `aaprp` enum('Y','N') NOT NULL DEFAULT 'N', `ipro` enum('Y','N') NOT NULL DEFAULT 'N', `division` enum('','Heavyweight','Cruiserweight','Light Heavyweight','Super Middleweight','Middleweight','Light Middleweight','Welterweight','Light Welterweight','Lightweight','Super Featherweight','Featherweight','Super Bantamweight','Bantamweight','Super Flyweight','Flyweight','Light Flyweight','Minimumweight') NOT NULL DEFAULT '', `ranking` double unsigned NOT NULL DEFAULT '0', `nationality` enum('','AF','AL','DZ','AS','AD','AO','AI','AQ','AG','AR','AM','AW','AU','AT','AZ','BS','BH','BD','BB','BY','BE','BZ','BJ','BM','BT','BO','BA','BW','BV','BR','IO','BN','BG','BF','BI','KH','CM','CA','CV','KY','CF','TD','CL','CN','HK','CX','CC','CO','KM','CG','CD','CK','CR','CI','HR','CU','CY','CZ','DK','DJ','DM','DO','TP','EC','EG','SV','GQ','ER','EE','ET','FK','FO','FJ','FI','FR','GF','PF','TF','GA','GM','GE','DE','GH','GI','GR','GL','GD','GP','GU','GT','GN','GW','GY','HT','HM','HN','HU','IS','IN','ID','IR','IQ','IE','IL','IT','JM','JP','JO','KZ','KE','KI','KR','KP','KW','KG','LA','LV','LB','LS','LR','LY','LI','LT','LU','MO','MK','MG','MW','MY','MV','ML','MT','MH','MQ','MR','MU','YT','MX','FM','MD','MC','MN','MS','ME','MA','MZ','MM','NA','NR','NP','AN','NL','NC','NZ','NI','NE','NG','NU','NF','MP','NO','OM','PK','PW','PA','PG','PY','PE','PH','PN','PL','PT','PR','QA','RE','RO','RU','RW','SH','KN','LC','PM','VC','WS','SM','ST','SA','SN','RS','SC','SL','SG','SK','SI','SB','SO','ZA','GS','ES','LK','SD','SR','SJ','SZ','SE','CH','SY','TW','TJ','TZ','TH','TG','TK','TL','TO','TT','TN','TR','TM','TC','TV','UG','UA','AE','UK','US','UM','UY','UZ','VU','VA','VE','VN','VG','VI','WF','EH','YE','YU','ZM','ZW','SP') NOT NULL, `height` tinyint(4) unsigned DEFAULT NULL, `reach` smallint(3) unsigned NOT NULL, `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `human_editor` varchar(8) CHARACTER SET utf8 NOT NULL DEFAULT 'johnshep', `alias` varchar(32) NOT NULL DEFAULT '', `residence_id` mediumint(6) unsigned NOT NULL DEFAULT '1', `birthplace_id` mediumint(8) unsigned NOT NULL, `r` double unsigned NOT NULL DEFAULT '0', `RD` double unsigned NOT NULL DEFAULT '0', `sex` enum('F','M') NOT NULL DEFAULT 'M', `birthplace` varchar(64) CHARACTER SET utf8 NOT NULL, `stance` enum('','O','S') NOT NULL DEFAULT '', `died` date NOT NULL DEFAULT '0000-00-00', `license` varchar(12) NOT NULL DEFAULT '', `fed_id` mediumint(6) unsigned zerofill DEFAULT NULL, `fed_state` char(2) CHARACTER SET ascii DEFAULT NULL, `career_start` date NOT NULL DEFAULT '0000-00-00', `career_end` date NOT NULL DEFAULT '0000-00-00', `is_boxer` enum('0','1','2','3') NOT NULL DEFAULT '0', `is_manager` enum('0','1','3') NOT NULL DEFAULT '0', `is_promoter` enum('0','1','3') NOT NULL DEFAULT '0', `is_matchmaker` enum('0','1','3') NOT NULL DEFAULT '0', `is_referee` enum('0','1','3') NOT NULL DEFAULT '0', `is_judge` enum('0','1','3') NOT NULL DEFAULT '0', `is_supervisor` enum('0','1','3') NOT NULL DEFAULT '0', `is_doctor` enum('0','1','3') NOT NULL DEFAULT '0', `is_inspector` enum('0','1','3') NOT NULL DEFAULT '0', PRIMARY KEY (`human_id`), KEY `r` (`r`), KEY `nationality` (`nationality`), KEY `license` (`license`), KEY `last_name_soundex` (`last_name_soundex`), KEY `first_last` (`first_name`,`last_name`), KEY `last_first` (`last_name`,`first_name`), KEY `ranking` (`ranking`), KEY `is_supervisor` (`is_supervisor`), KEY `is_boxer` (`is_boxer`), KEY `is_doctor` (`is_doctor`), KEY `is_judge` (`is_judge`), KEY `full_name_boxer` (`first_name_soundex`,`last_name_soundex`,`is_boxer`), KEY `division_boxer_sex_r` (`division`,`is_boxer`,`sex`,`r`), KEY `is_manager` (`is_manager`), KEY `towncity_id` (`residence_id`), KEY `fed_id` (`fed_id`), KEY `birthplace_id` (`birthplace_id`), FULLTEXT KEY `first_name_fulltext` (`first_name`), FULLTEXT KEY `last_name_fulltext` (`last_name`) ) ENGINE=MyISAM AUTO_INCREMENT=603120 DEFAULT CHARSET=latin1 PACK_KEYS=1

CREATE TABLE `towncities` ( `towncity_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `country_code` enum('','AF','AL','DZ','AS','AD','AO','AI','AQ','AG','AR','AM','AW','AU','AT','AZ','BS','BH','BD','BB','BY','BE','BZ','BJ','BM','BT','BO','BA','BW','BV','BR','IO','BN','BG','BF','BI','KH','CM','CA','CV','KY','CF','TD','CL','CN','HK','CX','CC','CO','KM','CG','CD','CK','CR','CI','HR','CU','CY','CZ','DK','DJ','DM','DO','TP','EC','EG','SV','GQ','ER','EE','EN','ET','FK','FO','FJ','FI','FR','GF','PF','TF','GA','GM','GE','DE','GH','GI','GR','GL','GD','GP','GU','GT','GN','GW','GY','HT','HM','HN','HU','IS','IN','ID','IR','IQ','IE','IL','IT','JM','JP','JO','KZ','KE','KI','KR','KP','KW','KG','LA','LV','LB','LS','LR','LY','LI','LT','LU','MO','MK','MG','MW','MY','MV','ML','MT','MH','MQ','MR','MU','YT','MX','FM','MD','MC','MN','ME','MS','MA','MZ','MM','NA','NR','NP','AN','NL','NC','NZ','NI','ND','NE','NG','NU','NF','MP','NO','OM','PK','PW','PA','PG','PY','PE','PH','PN','PL','PT','PR','QA','RE','RO','RU','RW','SH','KN','LC','PM','VC','WS','HI','SM','ST','CS','SA','SN','RS','SC','SL','SG','SK','SI','SB','SO','ZA','GS','ES','LK','SD','SR','SJ','SZ','SE','CH','SY','TW','TJ','TZ','TH','TG','TK','TO','TT','TN','TR','TM','TC','TV','UG','UA','AE','UK','US','UM','UY','UZ','VU','VA','VE','VN','VG','VI','WA','WF','EH','YE','YU','ZM','ZW','SP','PS','TL') CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL, `towncity_name` varchar(64) NOT NULL, `region_name` varchar(3) NOT NULL, `latitude` float NOT NULL DEFAULT '0', `longitude` float NOT NULL DEFAULT '0', PRIMARY KEY (`towncity_id`), KEY `country_city` (`country_code`,`region_name`) ) ENGINE=MyISAM AUTO_INCREMENT=41464 DEFAULT CHARSET=utf8

Thank you.

I tried to reproduce the problem with your table structures populated with synthetic data, and I am getting the same exact execution plan as you quoted, but the query itself only takes a fraction of second (0.01 sec with 600K records in `humans` and 40K records in `towncities`).

If it's still repeatable on your data, would it be possible for you to upload your data dumps to our FTP server (ftp://ftp.askmonty.org/)? If you could do that, please also include your cnf/ini file.

Thank you.

Note that condition only occurs with the limit condition in place ie LIMIT 275 , 25 just running the query without it returns the data fine

Hi Elena, files uploaded to private dir elena.tar.gz

John

Thank you :-)

all the best, John

FYI: The problem has been fixed, the fix will be included in the next 5.3 release, and in the 5.5 series release that is after the next one (5.5.23 was already building, so the fix didn't make it into 5.5.23)

s/5.5.23/5.5.24/. The fix will be in the release next after 5.5.24.