# Slow Numeric Primary Key lookups (inner join) **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [July 14, 2009, 5:04am UTC](https://forums.percona.com/t/slow-numeric-primary-key-lookups-inner-join/1210 "2009-07-14T05:04:39Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![barryhunter](https://avatars.discourse-cdn.com/v4/letter/b/6a8cbe/32.png) [@barryhunter](https://forums.percona.com/u/barryhunter)\ **Post date:** [July 14, 2009, 5:04am UTC](https://forums.percona.com/t/slow-numeric-primary-key-lookups-inner-join/1210/1 "2009-07-14T05:04:39Z")
select gi.\*,gi.realname as credit\_realname,if(gi.realname!=‘’,gi.realname,user.realname) as realname,user.realname as user\_realname,user.nickname from gridimage gi inner join user using(user\_id) where gridimage\_id=651728 limit 1; gridimage\_id is the primary key on gridimage table, as is user\_id on the user table. Explain: ±—±------------±------±------±----------------±--------±--------±------±-----±------+| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |±—±------------±------±------±----------------±--------±--------±------±-----±------+| 1 | SIMPLE | gi | const | PRIMARY,user\_id | PRIMARY | 4 | const | 1 | || 1 | SIMPLE | user | const | PRIMARY | PRIMARY | 4 | const | 1 | |±—±------------±------±------±----------------±--------±--------±------±-----±------+ Now why would this regually take over 10 seconds(!?!), as shown in the slow query log. I don’t think there is anything possible to optimize the query itself - its simply primary key lookups. There are many far more complex queries that run quickly. gridimage has about 1.3M records, and user 40k. … so guess looking at server settings somewhere? Let me know if any information I can post. ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [July 20, 2009, 6:01am UTC](https://forums.percona.com/t/slow-numeric-primary-key-lookups-inner-join/1210/2 "2009-07-20T06:01:02Z")
That query should run in milliseconds. What happens if you run this query manually? Maybe you are facing problems such as locking. Look at the slow qeury log for hints. ---
**Author:** ![barryhunter](https://avatars.discourse-cdn.com/v4/letter/b/6a8cbe/32.png) [@barryhunter](https://forums.percona.com/u/barryhunter)\ **Post date:** [July 22, 2009, 9:25am UTC](https://forums.percona.com/t/slow-numeric-primary-key-lookups-inner-join/1210/3 "2009-07-22T09:25:32Z")
Thanks for the reply! Dont think its locking, example # Query\_time: 21.283446 Lock\_time: 0.000149 Rows\_sent: 1 Rows\_examined: 0 SET timestamp=1248246943; select gi.\*,gi.realname as credit\_realname,if(gi.realname!=‘’,gi.realname,user.realname ) as realname,user.realname as user\_realname,user.nickname from gridi… There must be thousends of these queries happening daily absolutly fine (well not showing up in the slow query log) - but there are an uncomfortably large number of them. Looking though the slow log, very few of the long queries have high lock time. ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [July 23, 2009, 10:02am UTC](https://forums.percona.com/t/slow-numeric-primary-key-lookups-inner-join/1210/4 "2009-07-23T10:02:27Z")
Unfortunately I can think of no other reason than a very high load (cpu or disk). Still 21s is excessive. ---
**Author:** ![shantanuo](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/shantanuo/32/813_2.png) [@shantanuo](https://forums.percona.com/u/shantanuo)\ **Post date:** [July 29, 2009, 6:08am UTC](https://forums.percona.com/t/slow-numeric-primary-key-lookups-inner-join/1210/5 "2009-07-29T06:08:12Z")
I guess you need an index on gridimage\_id column. ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [July 29, 2009, 12:18pm UTC](https://forums.percona.com/t/slow-numeric-primary-key-lookups-inner-join/1210/6 "2009-07-29T12:18:05Z")
| [B]shantanuo wrote on Wed, 29 July 2009 13:38[/B] | | I guess you need an index on gridimage\_id column. | I guess that is the PK of the gi table. ---
**Author:** ![shantanuo](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/shantanuo/32/813_2.png) [@shantanuo](https://forums.percona.com/u/shantanuo)\ **Post date:** [July 29, 2009, 8:16pm UTC](https://forums.percona.com/t/slow-numeric-primary-key-lookups-inner-join/1210/7 "2009-07-29T20:16:38Z")
> > gridimage\_id is the primary key on gridimage table, as is user\_id on the user table. > > Sorry, I did not read this line. user\_id column key from gridimage table should be used in order to make the inner join faster. It shows up in the “Possible Keys” column of explain plan but is not used. ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [July 30, 2009, 4:52am UTC](https://forums.percona.com/t/slow-numeric-primary-key-lookups-inner-join/1210/8 "2009-07-30T04:52:31Z")
| [B]shantanuo wrote on Thu, 30 July 2009 03:46[/B] | | \>\> gridimage\_id is the primary key on gridimage table, as is user\_id on the user table. Sorry, I did not read this line. user\_id column key from gridimage table should be used in order to make the inner join faster. It shows up in the “Possible Keys” column of explain plan but is not used. | Incorrect, the user\_id column does not pose any restriction on the gi table but merely on the user table. This query can be executed with two primary key look-ups; one on gi to find the correct row, and then on user to find the matching row. So there is no good explanation why this query would be slow with the current execution plan.