# Any suggestions to tackle a slow query on a large table with composite PK?

**URL:** <https://forums.percona.com/t/any-suggestions-to-tackle-a-slow-query-on-a-large-table-with-composite-pk/478>\
**Category:** Other MySQL® Questions\
**Created:** [October 5, 2007, 1:18pm UTC](https://forums.percona.com/t/any-suggestions-to-tackle-a-slow-query-on-a-large-table-with-composite-pk/478 "2007-10-05T13:18:23Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![JGilbert](https://avatars.discourse-cdn.com/v4/letter/j/c89c15/32.png) [@JGilbert](https://forums.percona.com/u/JGilbert)\
**Post date:** [October 5, 2007, 1:18pm UTC](https://forums.percona.com/t/any-suggestions-to-tackle-a-slow-query-on-a-large-table-with-composite-pk/478/1 "2007-10-05T13:18:23Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![JGilbert](https://avatars.discourse-cdn.com/v4/letter/j/c89c15/32.png) [@JGilbert](https://forums.percona.com/u/JGilbert)\
**Post date:** [October 5, 2007, 1:22pm UTC](https://forums.percona.com/t/any-suggestions-to-tackle-a-slow-query-on-a-large-table-with-composite-pk/478/2 "2007-10-05T13:22:09Z")

</div>

The schema, for reference:

CREATE TABLE IF NOT EXISTS `ip2location` (  
`ip_first` int(10) unsigned NOT NULL,  
`ip_last` int(10) unsigned NOT NULL,  
`country_abbreviation` varchar(2) NOT NULL default ‘’,  
`country_name` varchar(42) NOT NULL default ‘’,  
`region` varchar(45) NOT NULL default ‘’,  
`city` varchar(37) NOT NULL default ‘’,  
`latitude` float default NULL,  
`longitude` float default NULL,  
`isp` varchar(103) NOT NULL default ‘’,  
PRIMARY KEY (`ip_first`,`ip_last`),  
KEY `ip_last` (`ip_last`),  
KEY `ip_first` (`ip_first`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [October 5, 2007, 4:22pm UTC](https://forums.percona.com/t/any-suggestions-to-tackle-a-slow-query-on-a-large-table-with-composite-pk/478/3 "2007-10-05T16:22:04Z")

</div>

The problem is the “WHERE first key part … AND second key part …” which MySQL handles by performing a range scan on the entire index which in your case is the primary key which in InnoDB basically means a table scan since data is stored in leaves.

Some info needed to try to solve your problem:  
Do you have ip\_first,ip\_last combinations that overlap anywhere?  
If you don’t then by tweaking your data a bit (if you have “holes” in you sequence) you can for example write your query like this:

…WHERE ip\_first \>= 123456ORDER BY ip\_first DESCLIMIT 1;

Which MySQL will be able to optimize to use only the ip\_first index and return a result very fast.

---

<div class="post-metadata">

**Author:** ![scoundrel](https://avatars.discourse-cdn.com/v4/letter/s/3ec8ea/32.png) [@scoundrel](https://forums.percona.com/u/scoundrel)\
**Post date:** [October 6, 2007, 9:51pm UTC](https://forums.percona.com/t/any-suggestions-to-tackle-a-slow-query-on-a-large-table-with-composite-pk/478/4 "2007-10-06T21:51:56Z")

</div>

Try to add a key: first\_last(first, last) and force mysql to use it. Then show us an explain results along with timing.

Notice: to force mysql to use some key, try USE INDEX(keyname) after the table name.

---

<div class="post-metadata">

**Author:** ![JGilbert](https://avatars.discourse-cdn.com/v4/letter/j/c89c15/32.png) [@JGilbert](https://forums.percona.com/u/JGilbert)\
**Post date:** [October 7, 2007, 2:24am UTC](https://forums.percona.com/t/any-suggestions-to-tackle-a-slow-query-on-a-large-table-with-composite-pk/478/5 "2007-10-07T02:24:35Z")

</div>

That would be essentially the PK since it is a composite between ip\_first and ip\_last. I deleted the two other indexes for this set, but left the PK

Status Time  
(initialization) 0.000005  
checking query cache for query 0.000195  
Opening tables 0.000032  
System lock 0.000025  
Table lock 0.000016  
init 0.000043  
optimizing 0.000019  
statistics 0.000138  
preparing 0.000087  
executing 0.000015  
Sending data 6.511645  
end 0.000044  
query end 0.000007  
freeing items 0.000017  
closing tables 0.000009  
logging slow query 0.001079

1 SIMPLE ip2location range PRIMARY PRIMARY 4 NULL 1864658 Using where

(1 total, Query took 4.7419 sec)

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [October 7, 2007, 7:17am UTC](https://forums.percona.com/t/any-suggestions-to-tackle-a-slow-query-on-a-large-table-with-composite-pk/478/6 "2007-10-07T07:17:31Z")

</div>

Did you read my post three posts up?

---

<div class="post-metadata">

**Author:** ![JGilbert](https://avatars.discourse-cdn.com/v4/letter/j/c89c15/32.png) [@JGilbert](https://forums.percona.com/u/JGilbert)\
**Post date:** [October 7, 2007, 9:18am UTC](https://forums.percona.com/t/any-suggestions-to-tackle-a-slow-query-on-a-large-table-with-composite-pk/478/7 "2007-10-07T09:18:43Z")

</div>

| [B]sterin wrote on Fri, 05 October 2007 17:52[/B] |
| 

Some info needed to try to solve your problem:  
Do you have ip\_first,ip\_last combinations that overlap anywhere?

 |

SELECT COUNT(\*) AS total\_rows, ip\_first  
FROM ip2location  
GROUP BY ip\_first  
HAVING total\_rows \>1;  
0 results

SELECT COUNT(\*) AS total\_rows, ip\_last  
FROM ip2location  
GROUP BY ip\_last  
HAVING total\_rows \>1;  
0 results

SELECT COUNT(\*) AS total\_rows, CONCAT\_WS(‘\_’,ip\_first,ip\_last) as first\_last  
FROM ip2location\_disk  
GROUP BY first\_last  
HAVING total\_rows \> 1;  
0 results (as you’d expect from a pk)

I didn’t realize before this that ip\_first and ip\_last were also unique on their own. Need to test more queries.

---

<div class="post-metadata">

**Author:** ![JGilbert](https://avatars.discourse-cdn.com/v4/letter/j/c89c15/32.png) [@JGilbert](https://forums.percona.com/u/JGilbert)\
**Post date:** [October 7, 2007, 9:36am UTC](https://forums.percona.com/t/any-suggestions-to-tackle-a-slow-query-on-a-large-table-with-composite-pk/478/8 "2007-10-07T09:36:58Z")

</div>

| [B]sterin wrote on Sun, 07 October 2007 08:47[/B] |
| Did you read my post three posts up? |

I’ve run several queries with different ips and the results are identical to ip\_first / last combo WHERE only you’re right, mysql optimized out the difference.

.00007 seconds is much improved. Thanks for helping me tackle this )