Poor performance on midsized site / Queries without index?
I can tell you that both of those are the semi-dynamic element queries that Craft uses to grab elements from the database.

The 2nd one is likely the result of someone calling a .total() or .count() on ElementCriteriaModel.

I see you ran EXPLAIN, but don't see the results from it.  You can use that as a starting point to see what index MySQL chose to execute that query and if it's not ideal.  Craft can't add an index to every content column (for obvious reasons), so if something in your templates is generating a SQL query frequently that causes MySQL to chose an index that isn't ideal and adding an index to content column would solve that for you, then that's something you can certainly do.


  I understand that the i18n tables are where the locales data is stored. Is there a way to disable a locale without losing any data?


No. It either exists or it doesn't.