Hello, I'm the maintainer of the Facebook-style Statuses module, or FBSS.

FBSS provides Flag integration so that users can "like" or report statuses. I had Flag enabled on the demo site so that users could test out the "like" feature, but I noticed recently that the demo site was running very slowly. After examining with Devel I was astounded to discover that uncached queries from my views were taking over 7000ms to run. Upon further examination, these queries contained complex joins on the Flag tables. I removed the relationships to the Flag tables through the Views UI and immediately the query time dropped over 2000%. I'm not the only one who experienced this: I was looking into the problem primarily because another user opened an issue about his site being slow in #757872: Improve views performance.

The relationships I was using were Flags: Facebook-style Statuses and Flags: Facebook-style Statuses flag counter. Both had the "Include only flagged content" setting disabled and were for the "like" flag that FBSS provides by default. The first relationship was set to operate for the Current user in the "By:" setting. I also had the Flag counter and Flag link fields enabled.

Unfortunately I didn't save the exact query, but I re-created one of the views that I had just now, and here's the "preview" query Views gave me:

SELECT facebook_status.sid AS sid,
   facebook_status.uid AS facebook_status_uid,
   facebook_status.pid AS facebook_status_pid,
   facebook_status.status AS facebook_status_status,
   facebook_status.status_time AS facebook_status_status_time,
   flag_counts_facebook_status.count AS flag_counts_facebook_status_count,
   flag_content.content_id AS flag_content_content_id,
   facebook_status.sid AS facebook_status_sid
 FROM 2foj_facebook_status facebook_status 
 LEFT JOIN 2foj_flag_content flag_content_facebook_status ON facebook_status.sid = flag_content_facebook_status.content_id AND (flag_content_facebook_status.fid = 2 AND flag_content_facebook_status.uid = ***CURRENT_USER***)
 INNER JOIN 2foj_flag_counts flag_counts_facebook_status ON facebook_status.sid = flag_counts_facebook_status.content_id AND (flag_counts_facebook_status.fid = 2 AND flag_counts_facebook_status.count > 0)
 LEFT JOIN 2foj_flag_content flag_content ON facebook_status.sid = flag_content.content_id AND (flag_content.fid = 2 AND flag_content.uid = ***CURRENT_USER***)
 WHERE ((facebook_status.status) != ('')) AND (facebook_status.pid != 617)
   ORDER BY facebook_status_sid DESC

To reproduce this, edit the default facebook_status_recent view that FBSS provides and add the relationships and fields that I mentioned. As you can see, it looks like there is an unnecessary second join on the {flag_content} table.

The demo site that I tested on has approximately 2500 statuses with 450 flags on them. FBSS provides Devel Generate integration if you want to quickly test this.

This issue is with reference to the current dev build of FBSS 6.2.x. For your convenience, FBSS' Views integration is defined here.

I'm using MySQL 5.0.81 and PHP 5.2.10. Let me know if there's any other information I can provide.

Comments

quicksketch’s picture

quicksketch
he/him
commented

I'd suggest you either use the 2.x version of Flag or apply the indexes that were added as part of #489610: Add index to 'count' in flag_count table. The only way to speed up joins is to add proper indexes, you should see if the newly added indexes from that issue help.

pribeh’s picture

pribeh commented

I will try this to upgrade my version of Flag to the latest beta-2. I've been using 2.x but can't remember which version.

icecreamyou’s picture

icecreamyou commented

I'm not concerned specifically about my site(s), but more about how feasible it is to use FBSS and Flag together. I will convert FBSS to use Flag 2.x soon, but in the mean time it looks like there is an issue here with the double-join on the {flag_content} table.

pribeh’s picture

pribeh commented

Just to report back. I'm definitely still seeing huge load times even after upgrading to 2.x-beta2. My views, which are Activity 2.x based, take roughly 2x as long or more to load with simply the "Flags: Node Flag" relationship added.

quicksketch’s picture

quicksketch
he/him
commented

Hmm, maybe try the key change suggested in #660316: Change primary key for flag_counts table? ;-)

pribeh’s picture

pribeh commented

Hi quicksketch. Thanks for recommending that. I tried changing the primary key in flag.install (for Flag 2.x-beta2), ran cron and flushed caches but without success. Then I tried uninstalling and reinstalling Flag with said changes but kept receiving the following error message:

Wrong datatype for second argument in /var/www/blah/testing.blah.ca/sites/all/modules/flag/includes/flag_handler_relationships.inc on line 89.

siharris’s picture

siharris commented

I was having similar trouble with a join on the flag_content table.

Just to report that simply clicking the "Index" button next to the content_id column in phpMyAdmin brought my query down from 3000ms to 5ms.

pribeh’s picture

pribeh commented

That's really interesting steeroy and thanks for sharing. I'll have to try that from command prompt - I don't have the luxury of phpMyAdmin :)

Flying Drupalist’s picture

Flying Drupalist commented

Are there any downsides to adding the index? If not then it should be made part of the module.

brisath’s picture

brisath commented

Subscribing

BenK’s picture

BenK commented

Subscribing...

mr.andrey’s picture

mr.andrey commented

subscribing...

pribeh’s picture

pribeh commented

Any updates? Would be pretty cool to use flag with views but it totally overloads my Drupal community sites - which "have" been streamlined :). Amazing module - thanks for all the hard work.

mooffie’s picture

mooffie commented

(IceCreamYou wrote that "FBSS provides Devel Generate integration", so I added it to my ToDo list to try this. Of course, if somebody provided us with a mysql dump (just the relevant tables) that would be easier.)

mr.andrey’s picture

mr.andrey commented

Those left joins are deadly. I ended up using Custom Field with PHP manual flag call.

quicksketch’s picture

quicksketch
he/him
commented

I'm plenty happy to review any suggestions here. Can someone with this problem attempt to fix it by adding/changing our indexes and keys? I'm surprised we're still having any sort of issue with how many keys we have already. Preferably, the queries being optimized would be just against the flag tables, so we know another table is not responsible for the slowness. Some EXPLAIN queries of the trouble queries and benchmarks would also be useful.

mooffie’s picture

mooffie commented

A note about the "Flag link" field: It adds a JOIN to your view's query. If this bothers you, switch to Flag Vista, whose "Flag link" field doesn't affect the query (it does its work in the pre_render() stage).

But, according to comment #4, people here are saying that merely adding the "Flags: Node Flag" relationship bogs down the system. In other words, it's not the "Flag link" they complain about.

pribeh’s picture

pribeh commented

@mr.andrey #15, could you maybe share the PHP you used in the custom field to pull the flag link?

@quickstketch #16, I'd like to provide that info for you. Can I get the info you need using devel?

@mooffie #17, interesting module. I'm trying to wrap my head around what it does. It provides a different Views implementation for the Flag module?

mooffie’s picture

mooffie commented

@mooffie #17, [Does "Flag Vista"] provides a different Views implementation for the Flag module?

Yes. But except for the "Flag link" field, which you say in comment #4 you aren't using, the SQL Flag Vista generates is about the same as Flag's. So it won't solve your performance problem.

@mr.andrey #15, could you maybe share the PHP you used in the custom field to pull the flag link?

(See the snippet on this page. (Calling flag_create_link() is not very cheap: it will load the node.))

perandre’s picture

perandre commented

Sub!

quicksketch’s picture

quicksketch
he/him
commented
Status: Active » Closed (duplicate)

Considering this issue doesn't have any actionable suggestions I'm marking it duplicate of these other issues which recommend new indexes:

#1105490: Add uid to content_type, content_id index for is_flagged()
#1146488: Additional index on flag_content