Re: Keys ignored for larger tables!?

[tamas marki]

Hi,

thanks for the quick reply.

Your index seems to have done the trick:

mysql> explain select id from arinrange where ip_start<=1582783255 and ip_end>=1582783255;
+----+-------------+-----------+-------+--------------------------------------------------+------------------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+--------------------------------------------------+------------------+---------+------+---------+--------------------------+
| 1 | SIMPLE | arinrange | range | iprange_ips_idx,iprange_ipe_idx,idx_start_end_id | idx_start_end_id | 5 | NULL | 1299610 | Using where; Using index |
+----+-------------+-----------+-------+--------------------------------------------------+------------------+---------+------+---------+--------------------------+

Though performance is still below what I expected (1-2 seconds for an uncached query).

Here are the tables:

mysql> show create table arinrange;
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| arinrange | CREATE TABLE `arinrange` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ip_start` int(10) unsigned DEFAULT NULL,
`ip_end` int(10) unsigned DEFAULT NULL,
`arin_data` text,
PRIMARY KEY (`id`),
KEY `iprange_ips_idx` (`ip_start`),
KEY `iprange_ipe_idx` (`ip_end`)
) ENGINE=MyISAM AUTO_INCREMENT=3480336 DEFAULT CHARSET=latin1 |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.18 sec)

mysql> show create table lacnicrange;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| lacnicrange | CREATE TABLE `lacnicrange` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ip_start` int(10) unsigned DEFAULT NULL,
`ip_end` int(10) unsigned DEFAULT NULL,
`lacnic_data` text,
PRIMARY KEY (`id`),
KEY `lacnicrange_ips_idx` (`ip_start`),
KEY `lacnicrange_ipe_idx` (`ip_end`)
) ENGINE=MyISAM AUTO_INCREMENT=94594 DEFAULT CHARSET=latin1 |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Any hints on how I could maximize performance (and minimize query durations) in this scenario would be welcome.

Thanks!