Optimize term_node and forum topic queries

I think in general that'd really help, and we need to try to get these two patches in for Views to work/be possible in D7 IMO.

In the specific case of taxonomy.module - since that'd more or less get us back to the D6 queries, and the D6 queries are terrible, we might be better off embracing the constraints here and going for denormalization anyway.

There's no hook_field_schema_alter() - and afaik that's by design, so a proper API for this seems quite far off. Also the way the taxonomy as field patch currently works, you can have more than one field per vocabulary, which would mean a lot of data duplication. I think materialized views is the proper answer to this but no chance of getting that into core this release.

Realised we don't need status on this table, because we should simply make sure that unpublished nodes don't show up in it, but we do need tracker2 style 'last_commented_or_updated' column if it's to be used for forums, so:

entity type | bundle | entity id | term id | created | sticky | last_activity

Then:

taxonomy_select_entities($tids, $conditions = array('entity type' => 'node'), $order_by = array('created', 'DESC'), $pager = TRUE);

Here's an initial, working, patch - it's currently based on, and includes #412518: Convert taxonomy_node_* related code to use field API + upgrade path.

I decided to abandon an entity-agnostic table - our only use case in core is taxonomy listings and forum topic listings, so we should do the minimum to support those, and let views / materialized views (and hopefully views and materalized views integration) handle other entity types when that's actually needed. That keeps the code here very simple - I'd hope to see both views and mv API move into core in the next couple of releases, in which case we can replace both this and the recently committed tracker indexing tables with those.

Done:

schema changes
taxonomy_field_*() hooks (and taxonomy_node_delete()).

TODO:
hook_update_N()
forum_comment_*() hooks to populate the 'changed' column + reworking forum queries to use this.

I don't think we need the same kind of indexing here as tracker tables - the only issue would be if you disabled then re-enabled forum and/or taxonomy modules for a while then had people posting comments on orphaned forum topics - as soon as any topic is update it's data will get refreshed in that edge case anyway.

I'm keeping this separate from the taxonomy_node_* patch for now, but will probably merge it back in when it's ready for review.

EXPLAIN on the current taxonomy_select_nodes() query and schema:

mysql> EXPLAIN SELECT * FROM taxonomy_index t WHERE (tid = 3) ORDER BY t.sticky DESC, t.created DESC;
+----+-------------+-------+------+----------------------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys              | key       | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+----------------------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | t     | ref  | term_node                          | term_node | 4       | const |    8 | Using where | 
+----+-------------+-------+------+----------------------------+-----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

And the patch.

Note if anyone has any ideas on how to get that query having no extra 'where' column, that'd be great. Initial investigations suggest it won't happen.

New update.

Now creates two tables:

{taxonomy_index}

{forum_index}

I was originally going to try to keep it to one table, but {forum_index} probably needs to account for the tablesort query if it's going to be really useful, which means more columns than we can reasonable be expected to maintain in taxonomy.module.

Added upgrade path for everything apart from forum data.

Didn't yet convert any forum queries to use the new table - that's up next.

Marking CNR for the bot to see if I managed to break anything along the way so far.

Promoting to bug report since afaik both of these are currently handled by custom code on Drupal.org to stop it falling over.

Giving up for the night. Have forum topic listings more or less working - this should also restore shadow forum topics (untested though).

Still needs upgrade path for forums and probably some more work on optimizing the topic listing query (and maybe convert some others), but this should pass tests now.

forum_get_topics queries now look like this:

mysql> EXPLAIN SELECT n.title, n.nid, n.sticky, n.uid, n.comment AS comment_mode, ncs.*, f.tid AS forum_tid, u.name, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name FROM node n INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid INNER JOIN forum f ON n.vid = f.vid INNER JOIN users u ON n.uid = u.uid INNER JOIN users u2 ON ncs.last_comment_uid = u2.uid WHERE n.nid IN (4, 6, 7);
+----+-------------+-------+--------+--------------------------+---------+---------+-------------------------+------+-------------+
| id | select_type | table | type   | possible_keys            | key     | key_len | ref                     | rows | Extra       |
+----+-------------+-------+--------+--------------------------+---------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | f     | index  | PRIMARY                  | tid     | 4       | NULL                    |    5 | Using index | 
|  1 | SIMPLE      | n     | eq_ref | PRIMARY,vid,uid          | vid     | 4       | d7.f.vid                |    1 | Using where | 
|  1 | SIMPLE      | ncs   | eq_ref | PRIMARY,last_comment_uid | PRIMARY | 4       | d7.n.nid                |    1 |             | 
|  1 | SIMPLE      | u2    | eq_ref | PRIMARY                  | PRIMARY | 4       | d7.ncs.last_comment_uid |    1 | Using where | 
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                  | PRIMARY | 4       | d7.n.uid                |    1 | Using where | 
+----+-------------+-------+--------+--------------------------+---------+---------+-------------------------+------+-------------+
5 rows in set (0.00 sec)

mysql> EXPLAIN SELECT f.* FROM forum_index f WHERE (f.tid = 4) ORDER BY f.sticky DESC, f.last_comment_timestamp DESC LIMIT 0, 25;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | f     | ref  | forum_topics  | forum_topics | 4       | const |    1 | Using where | 
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+

Merged this back into #511286: D7UX: Implement customizable shortcuts now that it's fully functional with upgrade path. So marking postponed on that issue dependent on what happens over there. Latest patch attached here too so we know where things were left off in case this needs re-rolling independently.

This got in with the Field API patch.