Hi

I am testing the process of adding a friend, and I was looking for data in flag_friend table, but the data is stored in flag_content table, is the table flag_friend not used?

Thanks

CommentFileSizeAuthor
#9 flag_friend_index-506412-9.patch1.87 KBmarkus_petrux

Comments

sirkitree’s picture

sirkitree commented
Status: Active » Closed (works as designed)

The flag_content table is used up until there is a friendship created. As soon as your flag is approved by another user, in which they also flag you, there are now two flags within flag_content. At this point we record the relationship within the flag_friend table and then completely remove both entries from the flag_content table.

pcambra’s picture

pcambra
he/him
Primary language Spanish
commented

Thanks for the answer.

Wouldn't make faster queries to duplicate this information in flag_friend table? I mean if uid 2 adds uid 3, then having two records will make the queries only to look up for uid and not for both uid and friend_uid.

By the way, what is the difference between FLAG_FRIEND_BOTH and FLAG_FRIEND_FLAGGED status?

sirkitree’s picture

sirkitree commented

I try to follow the DRY principle as much as possible: Don't Repeat Yourself - but have gone against it in cases of performance occasionally. I'm not sure how much it would speed it up really so if you can answer that we can go from there.

The difference between FLAG_FRIEND_BOTH and FLAG_FRIEND_FLAGGED:
FLAG_FRIEND_BOTH is set when both users are flagged.
FLAG_FRIEND_FLAGGED is set when both users are in the flag_friend table.

As soon as FLAG_FRIEND_BOTH is detected within hook_flag(), the flags are removed and the entry created in the flag_friend table thus making the next status detection FLAG_FRIEND_FLAGGED. So no other module should ever really be able to detect FLAG_FRIEND_BOTH.

Scott Reynolds’s picture

Scott Reynolds commented

I believe the goal of this table structure is to keep the table size small while still providing all the functionality.

This is a sacrifice of query speed for table size.

pcambra’s picture

pcambra
he/him
Primary language Spanish
commented

I see, you are right with the table size, records will be always twice, maybe it could be exposed somewhere as an option because the OR condition damages performance, I think.

So, if I need to check in my module if two users are friends with each other, I should check for FLAG_FRIEND_FLAGGED and NOT for FLAG_FRIEND_BOTH. Is that correct?

sirkitree’s picture

sirkitree commented

Correct

markus_petrux’s picture

markus_petrux commented
Status: Closed (works as designed) » Active

Please, let me re-open for consideration...

Here's a couple of tricks that can be used to optimize these queries:

1) Use 2 queries with UNION ALL, a query for each OR condition.
2) Use 2 separate statements. This can be used in DELETE operations, for example, making them faster as each one will use index.

sirkitree’s picture

sirkitree commented
Title: Table flag_friend. For what is used for? » Optimize queries on the flag_friend table.
Category: support » feature

Ok, let 's mark this issue as such then.

markus_petrux’s picture

markus_petrux commented
Status: Active » Needs review
StatusFileSize
new flag_friend_index-506412-9.patch1.87 KB

Well, here's a patch that tries to optimize this a little.

1) Adds an additional index to {flag_friend} table by friend_uid column. This index allows MySQL (since 5.0) optimize certain queries with index_merge access type.

2) Fixes a bug in DELETE FROM {flag_friend}, and then the query itself is also optimized by the new index.

This is how MySQL will resolve the queries:

Case 1:

// 2 queries using similar conditions:

$friends = db_result(db_query("SELECT * FROM {flag_friend} WHERE (uid = %d AND friend_uid = %d) OR (uid = %d AND friend_uid = %d)", $uid1, $uid2, $uid2, $uid1));

db_query('DELETE FROM {flag_friend} WHERE (uid = %d AND friend_uid = %d) OR (uid = %d AND friend_uid = %d)', $user->uid, $content_id, $content_id, $user->uid);

MySQL uses here the primary key using the range access method to resolve each OR condition. Both queries were working ok to date, and they are not affected by the attached patch.

Case 2:

$result = db_query("SELECT * FROM {flag_friend} WHERE uid = %d OR friend_uid = %d", $uid, $uid);

MySQL 5.0+ will use here index_merge. That means it will be able to use the primary key and the newly added index to resolve each condition, and then merge the results.

// This DELETE query is similar to the previous SELECT. But uses IN() and only 1 arg is passed. Bug!
db_query("DELETE FROM {flag_friend} WHERE uid IN (%d) OR friend_uid IN (%d)", $account->uid);

This one is fixed like this:

db_query("DELETE FROM {flag_friend} WHERE uid = %d OR friend_uid = %d", $account->uid, $account->uid);

And now, the WHERE clause is exactly the same as the SELECT, so it can benefit from the same performance optimization.

More information about MySQL Index Merge Optimization:

http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html
http://dev.mysql.com/doc/refman/5.1/en/index-merge-optimization.html

PostgreSQL also supports multiple indexes per query.

PS: My comment in #7 was based on the method that was necessary prior to this performance optimization (Index Merge) introduced in MySQL 5.

pcambra’s picture

pcambra
he/him
Primary language Spanish
commented

Hi

I've added a function for getting the number of friends of a given user (see #524966: Integration of flag_friend with panels) and the explain seems to benefit of the patch suggested by markus_petrus:

This is the query
SELECT COUNT(1) FROM {flag_friend} WHERE uid = %d OR friend_uid = %d

These are the explains:

Without the index:

+----+-------------+--------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table              | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | drupal_flag_friend | index | PRIMARY       | PRIMARY | 8       | NULL |   16 | Using where; Using index | 
+----+-------------+--------------------+-------+---------------+---------+---------+------+------+--------------------------+

With the index:

+----+-------------+--------------------+-------+--------------------+---------+---------+------+------+--------------------------+
| id | select_type | table              | type  | possible_keys      | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------------------+-------+--------------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | drupal_flag_friend | index | PRIMARY,friend_uid | PRIMARY | 8       | NULL |   16 | Using where; Using index | 
+----+-------------+--------------------+-------+--------------------+---------+---------+------+------+--------------------------+
markus_petrux’s picture

markus_petrux commented

@pcambra: with the new index, in the column 'type' you should see 'index_merge', and in the column 'extra', you should see 'Using union(PRIMARY,friend_uid); Using where'.

Are you testing on MySQL 5.0+? If so, then it might be possible that the index_merge optimization is discarded if your {flag_friend} table doesn't have many rows.

If you insert more rows for testing, you may also wish to run OPTIMIZE TABLE {flag_friend} so that the internal table statistics are updated for the MySQL optimizer to take the right decision.

pcambra’s picture

pcambra
he/him
Primary language Spanish
commented

Right, I've tested with 10 thousand records in flag_friend table and results have more sense:

mysql> EXPLAIN SELECT COUNT(1) FROM drupal_flag_friend WHERE uid = 9 OR friend_uid = 9;
+----+-------------+--------------------+-------------+--------------------+--------------------+---------+------+------+---------------------------------------------------+
| id | select_type | table              | type        | possible_keys      | key                | key_len | ref  | rows | Extra                                             |
+----+-------------+--------------------+-------------+--------------------+--------------------+---------+------+------+---------------------------------------------------+
|  1 | SIMPLE      | drupal_flag_friend | index_merge | PRIMARY,friend_uid | PRIMARY,friend_uid | 4,4     | NULL |   14 | Using sort_union(PRIMARY,friend_uid); Using where | 
+----+-------------+--------------------+-------------+--------------------+--------------------+---------+------+------+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> show session status like '%cost%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| Last_query_cost | 21.862190 | 
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> ALTER TABLE drupal_flag_friend DROP INDEX friend_uid;
Query OK, 10033 rows affected (0.22 sec)
Records: 10033  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT COUNT(1) FROM drupal_flag_friend WHERE uid = 9 OR friend_uid = 9;
+----+-------------+--------------------+-------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type | table              | type  | possible_keys | key     | key_len | ref  | rows  | Extra                    |
+----+-------------+--------------------+-------+---------------+---------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | drupal_flag_friend | index | PRIMARY       | PRIMARY | 8       | NULL | 10033 | Using where; Using index | 
+----+-------------+--------------------+-------+---------------+---------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

mysql> show session status like '%cost%';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 2040.442018 | 
+-----------------+-------------+
1 row in set (0.00 sec)
sirkitree’s picture

sirkitree commented
Status: Needs review » Fixed

Wow guys, thanks for the in depth explanations, examples and patch! I learned a lot in this thread. Comitted: http://drupal.org/cvs?commit=244194

Status: Fixed » Closed (fixed)

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