Add index to 'count' in flag_count table

Seems like a good idea to me.

Here's a patch

I combined this with #612602: Add index supporting the relationship in Views, as they both had the same update number. I put specifically in the 2.x branch since Drupal doesn't have any way of detecting if an index already exists and I didn't want to have conflicting updates between the 1.x branch and the 2.x branch. I'll try to hurry up the 2.x version for production use, as this seems like a pretty big improvement in our queries.

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?

@premanup: Open a new issue for that or at least set the status of this one to active again - otherwise this issue will look like fixed to everyone else.

Also - have you tested version 2 of Flag and seen if it has been fixed there?

@voxpelli: Oh, thank you for advice, I've just set the status to active.

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'),

premanup, you're recommending a different key change than this issue originally was created for. Please open a different issue explaining the situation.

@quicksketch, ok, I've opened it here http://drupal.org/node/660316