Lately, several of the Drupal sites I work on with large amounts of traffic have been showing many sluggish queries. One query that kept constantly showing up was this one:
SELECT * FROM variable;
Drupal stores most of it's settings in the variable table, but to improve query performance it stores all the variables from this table into a single record inside the cache table. However, when a variable is changed, added or deleted that cache must be updated.
The reason the above query was getting executed so frequently was because of a variable_set() call inside the Views module that was run on every page load.
I've seen this mistake more than once, so keep in mind that you should only add, change or delete variables during administrative tasks.