Re: Slow query

[Rick James]

OK, not surprise in the EXPLAIN EXTENDED:

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`)) |

Unfortunately, the type of query is hard to optimize.

Raising key_buffer_size may be the only significant improvement available.

This may help some, but minimizing the bulk of the temp table:

SELECT L.locId, L.city, L.latitude, L.longitude
    FROM  location AS L
    JOIN (
        SELECT location
            FROM ip2isp
            WHERE  INET_ATON('x.x.x.x') BETWEEN start AND end
         ) AS I  ON I.location = L.locId

To make that more efficient, change
KEY `isp2idx` (`start`,`end`),
to
KEY `isp2idx` (`start`,`end`, location),
(Then it can do the subquery entirely in the INDEX.)