While options are not a good plan for Drupal Core, there are a lot of benefits from properly using SQL_CACHE / SQL_NO_CACHE / HIGH_PRIORITY / SQL_CALC_FOUND_ROWS in custom code where the back-end database is a known type.
AFAICT there's no way to use query options with DBTNG. At best I can do
$query = db_select('foo', 'f')
->fields('', array('SQL_NO_CACHE'));
Which unfortunately produces:
SELECT .SQL_NO_CACHE ...
One way to implement this would be something like:
$query = db_select('foo', 'f')
->addOption('SQL_NO_CACHE')
->addOption('HIGH_PRIORITY');
Not all content is available!
It's likely this issue predates Contrib.social: some issue and comment data are missing.
Wonder after 10 years if this is still desired?
No, not still desired. If you are doing something that requires such high database performance, you are not going to use Drupal for.