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'),
CommentFileSizeAuthor
#2 flag_counts_primary_key.patch1.1 KBquicksketch

Comments

quicksketch’s picture

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

quicksketch’s picture

Status: Needs review » Fixed
StatusFileSize
new1.1 KB

Due 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!

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

benanne’s picture

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