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