0

I've checked numerous other SO posts and MySQL docs but can't seem to get an answer on why an index isn't being used, and how to force it to be used - I can see many others are having similar problems, but can't find a solution.

The table looks like this

CREATE TABLE `countries_ip` (
`ipfrom` INT(10) UNSIGNED ZEROFILL NOT NULL,
`ipto` INT(10) UNSIGNED ZEROFILL NOT NULL,
`countrySHORT` CHAR(2) NULL DEFAULT NULL,
`country_id` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`ipfrom`, `ipto`, `country_id`),
INDEX `from_to_index` (`ipfrom`, `ipto`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Not sure why the "from_to_index" is there - seems redundant to me. But anyway, the EXPLAIN query looks like this

EXPLAIN SELECT *
        FROM track_report t, countries_ip ip
        WHERE t.ip BETWEEN ip.ipfrom AND ip.ipto

and the result of the EXPLAIN is as follows:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  t   ALL getwebmaster    NULL    NULL    NULL    36291   
1   SIMPLE  ip  ALL PRIMARY,from_to_index   NULL    NULL    NULL    153914  Range checked for each record (index map: 0x3)

As you can see, the PRIMARY KEY from the countries_ip table isn't being used and so the query takes a LONG time (countries_ip has over 150k records)

I'm probably missing something simple, but any advice would be appreciated on how I can optimize this query. Thanks in advance.

Olaf Dietsche
  • 72,253
  • 8
  • 102
  • 198
spooky
  • 421
  • 5
  • 20

1 Answers1

1

It might help to define an index on track_report.ip. See SQL Fiddle.

I modified the where clause to do an explicit comparison, and now it uses the from_to_index.

SELECT ip
FROM track_report t, countries_ip ip
where t.ip >= ip.ipfrom and t.ip <= ip.ipto

See SQL Fiddle.

Olaf Dietsche
  • 72,253
  • 8
  • 102
  • 198
  • Hi Olaf, there already is an index on `track_report` - `getwebmaster` is a composite key where `ip` is the first field – spooky Dec 11 '12 at 11:39
  • @pavsid According to your explain it isn't used, whereas in the SQL Fiddle it uses the single column index, I defined. – Olaf Dietsche Dec 11 '12 at 11:42
  • True, but that's probably down to the optimiser in my EXPLAIN. Even if I add an index specifically to the `ip` field then the index does not get used (maybe due to size of table?) – spooky Dec 11 '12 at 12:01
  • @pavsid I played around a bit and now it uses the composite index. See modified answer. – Olaf Dietsche Dec 11 '12 at 12:21
  • it still doesn't use the index when the tables have data though - Sashi above suggested that but doesn't work. Even if I add `USE INDEX (PRIMARY)` then the index won't get used – spooky Dec 11 '12 at 13:05
  • I've just noticed however that doing `SELECT *` will not use the index, but doing `SELECT ip` does - in fact selecting any more than one column from the ip table will prevent the index from being used, but selecting just one will work! Is there any reason for this though? Is this enough to rely on? – spooky Dec 11 '12 at 13:10
  • @pavsid Then there's something else different in your setup. I just inserted some values in [SQL Fiddle](http://www.sqlfiddle.com/#!2/960de/3/1) and it uses the index with either `ip` or `*`. You might try it yourself. – Olaf Dietsche Dec 11 '12 at 13:12
  • You're using a different schema for countries_ip though Olaf - see http://www.sqlfiddle.com/#!2/b0a36/1 – spooky Dec 12 '12 at 11:38
  • @pavsid It is `countryshort` it seems, which is responsible. If you take it out, the primary key will be used. [SQL Fiddle](http://www.sqlfiddle.com/#!2/877c9/1/0) – Olaf Dietsche Dec 12 '12 at 12:50
  • Any idea why? Why should an extra column decide whether an index is used or not? – spooky Dec 12 '12 at 14:02
  • @pavsid No idea, sorry. The reason could range from some obscure MySQL internals to just a simple bug. – Olaf Dietsche Dec 12 '12 at 15:24
  • @pavsid I've opened a bug report at http://bugs.mysql.com/bug.php?id=67885; Let's see how they explain this. – Olaf Dietsche Dec 12 '12 at 17:45