$sql = db_rewrite_sql("
    SELECT s.value, s.send_interval, s.author_uid, s.send_comments, s.send_updates, n.title, n.status
    FROM {node} n
    INNER JOIN {subscriptions} s ON CAST(n.nid AS CHAR) = s.value
    WHERE s.module = 'node' AND s.field = 'nid' AND s.recipient_uid = %d
    ORDER BY s.author_uid");

the CAST in the subscriptions/node join condition means that MySQL cannot use indexes when joining the two tables, which is absolutely awful when you have a large node table.

is the CAST there for PostgreSQL compatibility of some kind? i suggest making this conditional on database engine. i don't think it's necessary at all for MySQL and, again, joining without an index is really awful.

you can also save a filesort on this query by adding a 2-column index on recipient_uid, author_uid to the subscriptions table.

Comments

salvis’s picture

salvis
he/his
commented
Status: Active » Fixed

Thank you, firebus — that's very good information. Yes, the CAST() is there for PostgreSQL compatibility. I've made it conditional on the database engine as you suggest.

I'm not sure the query is executed often enough to warrant adding an index for it, though.

Committed to both -dev versions (give them up to 12 hours to be repackaged).

Status: Fixed » Closed (fixed)

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