Closed (fixed)
Project:
Flag
Version:
6.x-1.1
Component:
Flag core
Priority:
Normal
Category:
Task
Assigned:
Unassigned
Reporter:
Created:
15 Dec 2009 at 15:24 UTC
Updated:
11 Mar 2012 at 00:53 UTC
Jump to comment: Most recent file
I build a block of popular users on my site using modules Views and Flag and discover it is a the longest SQL request in my life. It takes about 3 min.
There is SQL:
SELECT users.uid AS uid, users.picture AS users_picture, users.name AS users_name, flag_counts_users.count AS flag_counts_users_count, users.created AS users_created
FROM users users
LEFT JOIN flag_counts flag_counts_users ON users.uid = flag_counts_users.content_id
AND flag_counts_users.fid =2
WHERE (
users.status <>0
)
AND (
users.access !=0
)
ORDER BY flag_counts_users_count DESC , users_created ASC
I tried modify indexes for table flag_counts:
ALTER TABLE `flag_counts` DROP PRIMARY KEY;
ALTER TABLE `flag_counts` ADD PRIMARY KEY ( `fid` , `content_id` ) ;
After this the same SQL became much faster - 0.28 sec!
So I think the field 'content_type' is superfluous in primary key of table 'flag_counts', is not?
I didn't test version 2 but as far as I can see the function flag_schema using exactly the same definition of primary key for table 'flag_types' (line 300):
'primary key' => array('fid', 'content_type', 'content_id'),
So I guess It should be changed to
'primary key' => array('fid', 'content_id'),
| Comment | File | Size | Author |
|---|---|---|---|
| #2 | flag_counts_primary_key.patch | 1.1 KB | quicksketch |
Comments
Comment #1
quicksketchI think you're correct, considering that we already have additional indexes on FID-Content type and on Content type-Content ID. I can't see how having the primary key include the content type factors into anything, since the content type is already tied to the FID.
Comment #2
quicksketchDue to the difficulty of executing updates multiple times across different versions of Flag, I've only applied this fix to the 2.x branch. It should not affect your site if you continue to run with your modification and shouldn't conflict when you eventually upgrade to 2.x. Thanks for the report!
Comment #4
benanne commentedI know this has been fixed in the meantime, but I just wanted to say thanks for figuring this out. I'm still on flags 6.x-1.3 and I was getting tons of slow queries lately, this seems to have been the main cause. I modified the primary key as you said a few hours ago, and the number of slow queries is now down to practically zero.