InnoDB won't help, unless the software takes advantage of it. We aren't doing 'transactions', which InnoDB does MUCH better at. Also record level locking versus table level.
UBB and most forum software is heavily read intensive and MyISAM does that well..
Where it falls down is in the queries you mentioned above... the new 7.5.7 limits the query length to 50 characters ( reasonable value ) and discards crap like that one query was doing..
That alone will help huge...
Also, there are really two terribly inefficient queries that kill mysql.. They happen when there are very long topics ( lots of pages, so ubb uses an offset, which sucks ) and very large forum ( also lots of pagination )...
So just cleaning up showflat and postlist would take a HUGE load off any loaded server.
All in all, however.. RAM IS KING!! there is now way around that... to really run properly and with speed.. you should have enough ram to entirely cache your databases AND INDEXES into memory... whenever you have to hit the hard drive for any reason, you take two orders of magnitude hit in speed, if not more...
I never run with less than 8Gigs of ram ( a linux dedi with 8gb ram is $149/month )....
So favor RAM over CPU, if you are server shopping in the future and get as much RAM as possible... RAM RAM RAM
