With multisite, each blog has its own table. I want to create a plugin that will add custom taxonomies to all blogs. I know how to do this. Then I want to create a query to return all posts for a given taxonomy across all sites. Thus on the main site users can view "recent network posts for INSERT TAX-TERM HERE".
My first thought is to just do a
SELECT * ...
UNION ... etc. Obviously, I'd have to do
get_sites() (filtering for spam, etc.) and build the query using the ID to assign the right table name. Each union select would also have to inner join to get the right taxonomy. The right taxonomy which might change ID (but not name) from blog to blog. I can search on name but I would probably have to add an index to the table. My worry is that on a busy site with a lot of blogs the resultant SQL could be too demanding on the server.
My second idea is to create some sort of index table which gets updated when new posts are added. My worry is that on a busy site with a lot of blogs this could get messy if the index loses synchronicity or the whole index must be rebuilt. Basically, a lot of maintenance work. Not to mention the amount of core stuff I'd probably be replicating (a sign of bad code IMHO).
Which is the most efficient or stable way to do things?
Has anyone solved a similar problem before and what was your solution?