Hi. I have a query that executs slow. I have index on columns but still it is slow.
The query is:
mysql> explain SELECT locId, location.city as city,latitude,longitude FROM ip2isp join location on ip2isp.location=locId WHERE INET_ATON('x.x.x.x') BETWEEN start AND end;
+----+-------------+----------+--------+-------------------+---------+---------+---------------------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+-------------------+---------+---------+---------------------------------+---------+-------------+
| 1 | SIMPLE | ip2isp | ALL | isp2idx,start,end | NULL | NULL | NULL | 1126339 | Using where |
| 1 | SIMPLE | location | eq_ref | PRIMARY | PRIMARY | 4 | transfer_ro_pre.ip2isp.location | 1 | Using where |
+----+-------------+----------+--------+-------------------+---------+---------+---------------------------------+---------+-------------+
2 rows in set (0.00 sec)
mysql>
It seems like it dosn`t use indexes.
I create the indexes like:
mysql> alter table ip2isp add key using btree (start);
mysql> alter table ip2isp add key using btree (end);