MySQL Forums :: Performance :: Keys ignored for larger tables!?


Advanced Search

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!? 1619 tamas marki 04/06/2011 04:00AM
Re: Keys ignored for larger tables!? 706 Thomas Wiedmann 04/06/2011 04:52AM
Re: Keys ignored for larger tables!? 560 tamas marki 04/06/2011 05:04AM
Re: Keys ignored for larger tables!? 601 Thomas Wiedmann 04/06/2011 05:10AM
Re: Keys ignored for larger tables!? 535 tamas marki 04/06/2011 05:20AM
Re: Keys ignored for larger tables!? 603 Thomas Wiedmann 04/06/2011 05:35AM
Re: Keys ignored for larger tables!? 599 tamas marki 04/07/2011 04:23AM
Re: Keys ignored for larger tables!? 648 Thomas Wiedmann 04/07/2011 05:39AM
Re: Keys ignored for larger tables!? 621 tamas marki 04/07/2011 05:59AM
Re: Keys ignored for larger tables!? 531 Thomas Wiedmann 04/07/2011 07:08AM
Re: Keys ignored for larger tables!? 599 tamas marki 04/07/2011 07:17AM
Re: Keys ignored for larger tables!? 589 Thomas Wiedmann 04/07/2011 01:30PM
IP-address range is inefficient 1247 Rick James 04/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.