Add database index on twitter.created_time

I've been optimising MySQL on a server that is only running one Drupal site. Accordingly, I've been able to pick up when mysqltuner flags that there are queries running that do not have indices on columns that are JOINed.

One query that I was seeing in my logs is this one:

SELECT twitter.twitter_id AS twitter_id, twitter_account.followers_count AS twitter_account_followers_count, twitter_account.name AS twitter_account_name, twitter_account.screen_name AS twitter_account_screen_name, twitter.created_time AS twitter_created_time FROM twitter twitter
LEFT JOIN twitter_account twitter_account ON twitter.screen_name = twitter_account.screen_name
WHERE (( (twitter_account.protected = '0') )) ORDER BY twitter_created_time DESC

Both columns in the JOIN seemed to be indexed already. But the sort on twitter_created_time was not on an indexed column. That query was generated from the Views component that displays the recent tweets.So adding an index to twitter.created_time would help that query to run more efficiently.

I'm happy to write a patch that adds an _update_70xx function to the install file which adds the index. But I haven't time at the moment - so I thought I'd create an issue anyway. Since adding the index, this query has dropped out of my logs, so I'm in danger of forgetting my discovery that this column needs indexing. And, who knows, someone else may find time to write the patch before I get to it.