Slow Numeric Primary Key lookups (inner join)

[barryhunter]

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.

[gmouse]

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.

[barryhunter]

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.

[gmouse]

Unfortunately I can think of no other reason than a very high load (cpu or disk). Still 21s is excessive.

[shantanuo]

I guess you need an index on gridimage_id column.

[gmouse]

[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.

[shantanuo]

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.

[gmouse]

[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.