MySQL Forums
Forum List  »  Performance

Keys ignored for larger tables!?
Posted by: tamas marki
Date: April 06, 2011 04:00AM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
Keys ignored for larger tables!?
1660
April 06, 2011 04:00AM
1254
April 08, 2011 08:20PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.