select gi.*,gi.realname as credit_realname,if(gi.realname!='',gi.realname,use r.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.
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.
Comment