On my site, I have a query like the following:

    $nodes = db_query("SELECT DISTINCT(flag.content_id)
      FROM {flag_content} flag
      LEFT JOIN {node} node ON node.nid = flag.content_id
      WHERE node.status = 1 AND flag.uid = :uid", array(
        ':uid' => $uid,
    ))->fetchCol();

With the current flag indexes, the query below takes an increasingly longer amount of time (see EXPLAIN results after query below), right now about 80-120ms per query, but increasing as our table grows beyond 500,000 flag records:

SELECT flag.content_id FROM flag_content flag LEFT JOIN node node ON node.nid = flag.content_id WHERE node.status = 1 AND flag.uid = :uid
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	flag	index	content_id_fid	fid_content_id_uid_sid	14		213100	Using where; Using index
1	SIMPLE	node	eq_ref	PRIMARY,node_status_type	PRIMARY	4	flocknote.flag.content_id	1	Using where

If I simply add an index on flag.uid and flag.content_id, the query takes about .5ms (about 180x faster!). Could you consider adding this index? Patch will be attached in first comment.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

geerlingguy’s picture

Status: Active » Needs review
FileSize
935 bytes

After attached patch is applied, and update.php is run, query takes on average 0.52ms (instead of 90ms):

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	flag	ref	content_id_fid,uid_content_id	uid_content_id	4	const	17	Using index; Using temporary
1	SIMPLE	node	eq_ref	PRIMARY,node_status_type	PRIMARY	4	flocknote.flag.content_id	1	Using where; Distinct
joachim’s picture

Title: Missing index on flag_content for uid and content_id » Add index on {flagging} for uid and entity_id
Version: 7.x-2.x-dev » 7.x-3.x-dev
Category: bug » feature
Status: Needs review » Needs work

This will have to be fixed on 3.x first and backported.

There are already several indexes on this table, and AFAIK there comes a point where adding more indexes is counterproductive:

    'indexes' => array(
      'entity_type_uid_sid' => array('entity_type', 'uid', 'sid'),
      'entity_type_entity_id_uid_sid' => array('entity_type', 'entity_id', 'uid', 'sid'),
      'entity_id_fid' => array('entity_id', 'fid'),
    ),

Could do with some input from people who know more about database performance than I do.

geerlingguy’s picture

Status: Needs work » Needs review
FileSize
793 bytes

Attached patch adds the index properly for {flagging} table in 7.x-3.x.

geerlingguy’s picture

Issue summary: View changes

Updated text.

joelpittet’s picture

comment can be updated from content_id to entity_id, but otherwise this is RTBC. And bump the update hook.

I swapped the index name around so that it read the same as the other one. so entity_id_uid instead of uid_entity_id. Just found this, sorry that it's so old, but thanks nonetheless.

joelpittet’s picture

The last submitted patch, 4: uid-content_id-index_1781536-4.patch, failed testing.