Posted by fajerstarter on June 12, 2009 at 8:58am
| Project: | Flag |
| Version: | 6.x-2.0-beta1 |
| Component: | Flag core |
| Category: | task |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | closed (fixed) |
Issue Summary
I run an EXPLAIN on query that did a sort on the 'count' table in 'flag_count', which should be relatively common thing to do, e.g in Views. It gave a "using filesort". I fixed this by adding an index to the 'count' column.
Comments
#1
Seems like a good idea to me.
#2
Here's a patch
#3
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.
#4
Automatically closed -- issue fixed for 2 weeks with no activity.
#5
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_createdFROM 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?
#6
@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?
#7
@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):
<?php'primary key' => array('fid', 'content_type', 'content_id'),
?>
So I guess It should be changed to
<?php'primary key' => array('fid', 'content_id'),
?>
#8
premanup, you're recommending a different key change than this issue originally was created for. Please open a different issue explaining the situation.
#9
@quicksketch, ok, I've opened it here http://drupal.org/node/660316