Bug #28612 Optimizer does not use index for BETWEEN in a JOIN condition

• Submitted: 23 May 2007 7:30
• Modified: 24 Jun 2007 13:27
• Reporter: Ondra Zizka
• Status: No Feedback
• Category: MySQL Server: Optimizer
• Severity: S5 (Performance)
• Version: 5.0.37
• OS: Any
• CPU Architecture: Any
• Tags: between, INDEX, Indices, keys, left join, Optimizer

[23 May 2007 7:30] Ondra Zizka

Description:
When using BETWEEN in a LEFT JOIN condition, the index is not used for the column subjecting to BETWEEN. When used with >= AND <=, index is used.

How to repeat:
DROP TABLE IF EXISTS `stats_download`;
CREATE TABLE  `stats_download` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `lrc_id` int(10) unsigned NOT NULL default '0',
  `tm` int(10) unsigned NOT NULL default '0',
  `ip` int(10) unsigned NOT NULL default '0',
  `country_code` smallint(6) unsigned NOT NULL default '0',
  `reg_num` int(10) unsigned NOT NULL default '0',
  `progver` smallint(5) unsigned NOT NULL default '0',
  `err` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `prog_ver` (`progver`),
  KEY `err` (`err`),
  KEY `lrc_id` (`lrc_id`),
  KEY `tm` (`tm`),
  KEY `ip` (`ip`),
  KEY `country_code` (`country_code`),
  KEY `reg_num` (`reg_num`)
) ENGINE=MyISAM;

CREATE TABLE stats_download_locations (
  id INT UNSIGNED NOT NULL PRIMARY KEY,
  id_location INT UNSIGNED, INDEX (id_location)
);

DROP TABLE IF EXISTS `gi_blocks`;
CREATE TABLE  `gi_blocks` (
  `start_ip` int(10) unsigned NOT NULL default '0',
  `end_ip` int(10) unsigned NOT NULL default '0',
  `id_location` int(10) unsigned NOT NULL default '0',
  KEY `si` (`start_ip`),
  KEY `ei` (`end_ip`)
) ENGINE=MyISAM COMMENT='IP ranges';

EXPLAIN
SELECT sd.id, gb.id_location FROM stats_download AS sd
LEFT JOIN geoip.gi_blocks AS gb ON sd.ip >= gb.start_ip AND sd.ip <= gb.end_ip
WHERE sd.id BETWEEN 29000000 AND 29001000;

EXPLAIN
SELECT sd.id, gb.id_location FROM stats_download AS sd
LEFT JOIN geoip.gi_blocks AS gb ON sd.ip BETWEEN gb.start_ip AND gb.end_ip
WHERE sd.id BETWEEN 29000000 AND 29001000;

While the first SELECT has 'si,ei' AS possible_keys for the table 'gb',
the second has NULL.

Suggested fix:
Let BETWEEN use the indices like >= and <= would use.

[24 May 2007 13:27] Valeriy Kravchuk

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.41, and, in case of the same problem, send also some data/INSERT statements that demonstrates the prbolem described.

[24 Jun 2007 23:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".