Early Bird Registration for DrupalCon Portland 2024 is open! Register by 23:59 PST on 31 March 2024, to get $100 off your ticket.
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.
Comment | File | Size | Author |
---|---|---|---|
#5 | uid-content_id-index_1781536-4.patch | 628 bytes | joelpittet |
| |||
#5 | interdiff.txt | 825 bytes | joelpittet |
Comments
Comment #1
geerlingguy CreditAttribution: geerlingguy commentedAfter attached patch is applied, and update.php is run, query takes on average 0.52ms (instead of 90ms):
Comment #2
joachim CreditAttribution: joachim commentedThis 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:
Could do with some input from people who know more about database performance than I do.
Comment #3
geerlingguy CreditAttribution: geerlingguy commentedAttached patch adds the index properly for
{flagging}
table in 7.x-3.x.Comment #3.0
geerlingguy CreditAttribution: geerlingguy commentedUpdated text.
Comment #4
joelpittetcomment can be updated from
content_id
toentity_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 ofuid_entity_id
. Just found this, sorry that it's so old, but thanks nonetheless.Comment #5
joelpittetThat was an interdiff, my bad.