Actual query:SELECT SQL_NO_CACHE country_abbreviation, country_name, region, city, isp, latitude, longitude FROM ip2location WHERE ip_first <= 3335238065 AND ip_last >= 3335238065; 4.6 secondsProfile:Status Time(initialization) 0.000004checking query cache for query 0.000073Opening tables 0.000014System lock 0.000022Table lock 0.00001init 0.000032optimizing 0.000015statistics 0.000213preparing 0.000131executing 0.000011Sending data 4.651696 <— What? it’s 1 recordend 0.000047query end 0.000008freeing items 0.000019closing tables 0.000011logging slow query 0.000004Explain:1 SIMPLE ip2location range PRIMARY,ip_last,ip_first PRIMARY 4 NULL 1886656 Using whereResearch:SELECT SQL_NO_CACHE ip_first FROM ip2location WHERE ip_last >= 3335238065; .0007 secondsSELECT SQL_NO_CACHE ip_last FROM ip2location WHERE ip_first <= 3335238065; .0007 secondsSELECT SQL_NO_CACHE count(ip_last) FROM ip2location WHERE ip_first <=3335238065; 2,251,823 rows (1.3 seconds)SELECT SQL_NO_CACHE count(ip_first) FROM ip2location WHERE ip_last >= 3335238065; 1,406,113 rows (.65 seconds)SELECT SQL_NO_CACHE max(ip_first) AS first_max FROM ip2location WHERE ip_last >= 3335238065; 4278190080 (1 second)SELECT SQL_NO_CACHE min(ip_first) AS first_min FROM ip2location WHERE ip_last >= 3335238065; 3335237632 (.69 seconds)SELECT SQL_NO_CACHE max(ip_last) AS last_max FROM ip2location WHERE ip_first <= 3335238065; 3335239167 (1.4 seconds)SELECT SQL_NO_CACHE min(ip_last) AS last_min FROM ip2location WHERE ip_first <= 3335238065; 33996343 (1.37 seconds)
The table itself has a composite PK on ip_first and ip_last. I wasn’t sure if it was not correctly using the keys because it was a composite, so i added a normal index on both ip_last and ip_first separately. Initially I thought I saw a 2-4 second improvement in query performance, however, that was months ago. to my knowledge queries like this have not ever run under 4 seconds though. This is a query which runs when someone first visits the site which causes a 5-10 second delay in some cases. It would be great if I could find a way to tune this down somehow to around or below 1 second.
Thanks in advance for any help!