Keys ignored for larger tables!?

[tamas marki]

Hello,

I have 3 tables with IP ranges. I created indexes for the ip_start and ip_end columns, but they are ignored for the 2 larger tables and only used with the single table. Here are two:


mysql> describe lacnicrange;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ip_start | int(10) unsigned | YES | MUL | NULL | |
| ip_end | int(10) unsigned | YES | MUL | NULL | |
| lacnic_data | text | YES | | NULL | |
+-------------+------------------+------+-----+---------+----------------+


mysql> describe arinrange;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ip_start | int(10) unsigned | YES | MUL | NULL | |
| ip_end | int(10) unsigned | YES | MUL | NULL | |
| arin_data | text | YES | | NULL | |
+-----------+------------------+------+-----+---------+----------------+

The indexes:

mysql> show index in lacnicrange;
+-------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| lacnicrange | 0 | PRIMARY | 1 | id | A | 94593 | NULL | NULL | | BTREE | |
| lacnicrange | 1 | lacnicrange_ips_idx | 1 | ip_start | A | NULL | NULL | NULL | YES | BTREE | |
| lacnicrange | 1 | lacnicrange_ipe_idx | 1 | ip_end | A | NULL | NULL | NULL | YES | BTREE | |
+-------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

mysql> show index in arinrange;
+-----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| arinrange | 0 | PRIMARY | 1 | id | A | 1990701 | NULL | NULL | | BTREE | |
| arinrange | 1 | iprange_ips_idx | 1 | ip_start | A | NULL | NULL | NULL | YES | BTREE | |
| arinrange | 1 | iprange_ipe_idx | 1 | ip_end | A | NULL | NULL | NULL | YES | BTREE | |
+-----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+


However, a query for arinranges doesn't use the keys:

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 | ALL | iprange_ips_idx,iprange_ipe_idx | NULL | NULL | NULL | 1990701 | Using where |
+----+-------------+-----------+------+---------------------------------+------+---------+------+---------+-------------+


While the same query on lacnicrange does use the index:

mysql> explain select id from lacnicrange where ip_start<=1582783255 and ip_end>=1582783255;
+----+-------------+-------------+-------+-----------------------------------------+---------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+-----------------------------------------+---------------------+---------+------+------+-------------+
| 1 | SIMPLE | lacnicrange | range | lacnicrange_ips_idx,lacnicrange_ipe_idx | lacnicrange_ips_idx | 5 | NULL | 460 | Using where |
+----+-------------+-------------+-------+-----------------------------------------+---------------------+---------+------+------+-------------+


The only difference is that arinrange has 1990701 rows, lacnicrange only 94593. Of course this causes queries to run too slowly.

Could someone possibly shed some light on this? I'm really getting frustrated.

Thanks!