I noticed that there a handful of queries that ExpressionEngine runs that don't use any database indexes. An example is the query
SELECT DISTINCT ee.* FROM exp_extensions ee WHERE enabled = 'y' ORDER BY hook, priority ASC, class which gets run on every page. Doing a MySQL
EXPLAIN on this shows that it could benefit from a database index. However, since the number of extensions installed is usually fairly small, adding an index for this query might not be a huge win.
I realize that the official stance is to not add indexes because it could conflict with future updates. On the other hand, from a practical standpoint, I have to balance resource usage and page rendering speed against the official stance; also, I'm willing and able to removing indexes before upgrading to a newer version of ExpressionEngine.
Is there a set of queries that are prime candidates for database indexes that would reduce load on the MySQL server? Is there any best practice on this?