Re: Slow query

[Dragos Gabriel Iancu]

Results are:

mysql> EXPLAIN EXTENDED 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\g
+----+-------------+----------+--------+-----------------------------------------+-------------------+---------+---------------------------------+--------+----------+--------------------------+
| id | select_type | table    | type   | possible_keys                           | key               | key_len | ref                             | rows   | filtered | Extra                    |
+----+-------------+----------+--------+-----------------------------------------+-------------------+---------+---------------------------------+--------+----------+--------------------------+
|  1 | SIMPLE      | ip2isp   | range  | end,idx_start_end_loc,idx_loc_start_end | idx_start_end_loc | 4       | NULL                            | 685619 |    66.68 | Using where; Using index |
|  1 | SIMPLE      | location | eq_ref | PRIMARY                                 | PRIMARY           | 4       | transfer_ro_pre.ip2isp.location |      1 |   100.00 | Using where              |
+----+-------------+----------+--------+-----------------------------------------+-------------------+---------+---------------------------------+--------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `transfer_ro_pre`.`location`.`locId` AS `locId`,`transfer_ro_pre`.`location`.`city` AS `city`,`transfer_ro_pre`.`location`.`latitude` AS `latitude`,`transfer_ro_pre`.`location`.`longitude` AS `longitude` from `transfer_ro_pre`.`ip2isp` join `transfer_ro_pre`.`location` where ((inet_aton('x.x.x.x') between `transfer_ro_pre`.`ip2isp`.`start` and `transfer_ro_pre`.`ip2isp`.`end`) and (`transfer_ro_pre`.`ip2isp`.`location` = `transfer_ro_pre`.`location`.`locId`)) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>