Optimizing a specific Views-generated SQL query. More information at http://thereisamoduleforthat.com/content/view-nodes-and-their-translations-part-2-optimizing-views-generated-query.

demo_i18n.module

<?php

/**
 * Implements hook_views_query_alter().
 */
function demo_i18n_views_query_alter(&$view, &$query) {
  unset($query->relationships['node_node']);
  unset($query->relationships['node_node_1']);
  unset($query->relationships['node_node_2']);
  unset($query->table_queue['node_node']);
  unset($query->table_queue['node_node_1']);
  unset($query->table_queue['node_node_2']);
  unset($query->fields['node_node_1_title']);
  unset($query->fields['node_node_1_nid']);
  unset($query->fields['node_node_1_language']);
  unset($query->fields['node_node_1_title']);
  unset($query->fields['node_node_2_nid']);
  unset($query->fields['node_node_2_language']);
  unset($query->fields['node_node_2_title']);
  unset($query->fields['node_node_nid']);
  unset($query->fields['node_node_language']);
  unset($query->fields['node_node_title']);
}

/**
 * Implements hook_views_post_execute().
 */
function demo_i18n_views_post_execute(&$view) {
  $map = array();
  foreach ($view->result as $i => $result) {
    $map[$result->nid] = $i;
  }
  $translations = db_query("
    SELECT node.tnid, node.nid, node.title, node.language
    FROM {node}
    WHERE node.tnid IN (:nids) OR node.nid IN (:nids)
  ", array(':nids' => array_keys($map)));
  foreach ($translations as $t) {
    $t = (object)$t;
    switch ($t->language) {
      case 'ar':
        $prefix = '';
        break;
      case 'en':
        $prefix = '_1';
        break;
      case 'fr':
        $prefix = '_2';
        break;
    }
    if (empty($t->tnid)) {
      $result = &$view->result[$map[$t->nid]];
    } else {
      $result = &$view->result[$map[$t->tnid]];
    }
    $result->{"node_node{$prefix}_nid"} = $t->nid;
    $result->{"node_node{$prefix}_title"} = $t->title;
    $result->{"node_node{$prefix}_language"} = $t->language;
  }
}