SQL query that you need to optimize

Right! Committed this patch to 6.x, thanks!

Well, here is a quick look at all the use of this table in the queries in the module. From what I see most queries directly use some column from the table, others count values in this table, while the rest joins through this table to get translations for the given strings.

$ grep -r "{l10n_community_string}" *
l10n_community/ajax.inc:  $result = db_query('SELECT p.pid, p.title project_title, r.rid, r.title release_title, COUNT(l.lineno) as occurance_count FROM {l10n_community_project} p INNER JOIN {l10n_community_release} r ON p.pid = r.pid INNER JOIN {l10n_community_file} f ON r.rid = f.rid INNER JOIN {l10n_community_line} l ON f.fid = l.fid INNER JOIN {l10n_community_string} s ON l.sid = s.sid WHERE s.sid = %d GROUP BY r.rid ORDER by p.pid, r.rid', $sid);
l10n_community/export.inc:    $sql = 'SELECT s.sid, s.value, f.location, f.revision, l.lineno, l.type FROM {l10n_community_release} r INNER JOIN {l10n_community_file} f ON r.rid = f.rid INNER JOIN {l10n_community_line} l ON f.fid = l.fid INNER JOIN {l10n_community_string} s ON l.sid = s.sid WHERE r.pid = %d';
l10n_community/export.inc:    $sql = "SELECT s.sid, s.value, f.location, f.revision, l.lineno, l.type, t.translation, t.uid_approved, t.time_approved FROM {l10n_community_release} r INNER JOIN {l10n_community_file} f ON r.rid = f.rid INNER JOIN {l10n_community_line} l ON f.fid = l.fid INNER JOIN {l10n_community_string} s ON l.sid = s.sid  LEFT JOIN {l10n_community_translation} t ON s.sid = t.sid AND t.language = '%s' AND is_active = 1 AND is_suggestion = 0 WHERE r.pid = %d";
l10n_community/extractor.inc:    if (!$sid = db_result(db_query("SELECT sid FROM {l10n_community_string} WHERE value = BINARY '%s'", $value))) {
l10n_community/extractor.inc:      db_query("INSERT INTO {l10n_community_string} (value) VALUES ('%s')", $value);
l10n_community/extractor.inc:      $sid = db_result(db_query("SELECT sid FROM {l10n_community_string} WHERE value = BINARY '%s'", $value));
l10n_community/import.inc:    if ($sid = db_result(db_query("SELECT sid FROM {l10n_community_string} WHERE BINARY value = '%s'", $value['msgid']))) {
l10n_community/l10n_community.admin.inc:    'DELETE s, t FROM {l10n_community_string} s
l10n_community/l10n_community.admin.inc:  return db_result(db_query('SELECT COUNT(*) FROM {l10n_community_string} s LEFT JOIN {l10n_community_line} l ON s.sid = l.sid WHERE l.sid IS NULL'));
l10n_community/l10n_community.install:        'description' => $t('Reference to the {l10n_community_string}.sid found on this line.'),
l10n_community/l10n_community.install:        'description' => $t('Reference to the {l10n_community_string}.sid which is being translated.'),
l10n_community/l10n_community.install:    $result = db_query("SELECT s.sid, s.value, t.tid, t.translation FROM {l10n_community_string} s INNER JOIN {l10n_community_translation} t ON s.sid = t.sid WHERE s.value LIKE '%s%%' OR  s.value LIKE '%%%s' OR t.translation LIKE '%s%%' OR t.translation LIKE '%%%s'", $char, $char, $char, $char);
l10n_community/l10n_community.module:  $existing_string = db_fetch_object(db_query("SELECT s.value, t.sid, t.tid, t.translation FROM {l10n_community_string} s LEFT JOIN {l10n_community_translation} t ON s.sid = t.sid WHERE s.sid = %d AND ((t.sid IS NULL) OR (t.language = '%s' AND t.is_suggestion = 0 AND t.is_active = 1))", $sid, $langcode));
l10n_community/l10n_community.module:  return (bool) db_result(db_query("SELECT s.sid FROM {l10n_community_string} s LEFT JOIN {l10n_community_translation} t ON s.sid = t.sid WHERE t.translation = '%s' AND t.is_active = 1 AND t.language = '%s' AND s.sid = %d", $suggestion, $langcode, $sid));
l10n_community/l10n_community.module:      $stats['strings'] = db_result(db_query('SELECT COUNT(*) FROM {l10n_community_string}'));
l10n_community/l10n_community.module:      $stats['strings'] = db_result(db_query('SELECT COUNT(*) FROM {l10n_community_string}'));
l10n_community/pages.inc:      return db_result(db_query("SELECT COUNT(sid) FROM {l10n_community_string}"));
l10n_community/pages.inc:      return db_result(db_query('SELECT COUNT(DISTINCT s.sid) FROM {l10n_community_release} r INNER JOIN {l10n_community_file} f ON r.rid = f.rid INNER JOIN {l10n_community_line} l ON f.fid = l.fid INNER JOIN {l10n_community_string} s ON l.sid = s.sid WHERE r.pid = %d', $id));
l10n_community/pages.inc:        $count_sql = "SELECT COUNT(DISTINCT t.sid) AS translation_count, t.language, t.is_suggestion FROM {l10n_community_release} r INNER JOIN {l10n_community_file} f ON r.rid = f.rid INNER JOIN {l10n_community_line} l ON f.fid = l.fid INNER JOIN {l10n_community_string} s ON l.sid = s.sid LEFT JOIN {l10n_community_translation} t ON s.sid = t.sid WHERE r.pid = %d AND t.is_active = 1 AND t.translation != '' GROUP BY t.language, t.is_suggestion";
l10n_community/pages.inc:      $result = db_query("SELECT COUNT(DISTINCT s.sid) AS string_count, p.pid, p.title, p.uri FROM {l10n_community_project} p INNER JOIN {l10n_community_release} r ON p.pid = r.pid INNER JOIN {l10n_community_file} f ON r.rid = f.rid INNER JOIN {l10n_community_line} l ON f.fid = l.fid INNER JOIN {l10n_community_string} s ON l.sid = s.sid GROUP BY p.pid;");
l10n_community/pages.inc:      $count_sql = "SELECT COUNT(DISTINCT t.sid) AS translation_count, r.pid, t.is_suggestion FROM {l10n_community_release} r INNER JOIN {l10n_community_file} f ON r.rid = f.rid INNER JOIN {l10n_community_line} l ON f.fid = l.fid INNER JOIN {l10n_community_string} s ON l.sid = s.sid LEFT JOIN {l10n_community_translation} t ON s.sid = t.sid WHERE t.is_active = 1 AND t.translation != '' ";
l10n_community/translate.inc:    $sql = "SELECT DISTINCT s.sid, s.value, t.tid, t.language, t.translation, t.uid_entered, t.uid_approved, t.time_entered, t.time_approved, t.has_suggestion, t.is_suggestion, t.is_active FROM {l10n_community_string} s LEFT JOIN {l10n_community_translation} t ON s.sid = t.sid AND t.language = '%s' AND t.is_active = 1 AND t.is_suggestion = 0 WHERE";
l10n_community/translate.inc:    $sql_count = "SELECT COUNT(DISTINCT(s.sid)) FROM {l10n_community_string} s LEFT JOIN {l10n_community_translation} t ON s.sid = t.sid AND t.language = '%s' AND t.is_active = 1 AND t.is_suggestion = 0 WHERE";
l10n_community/translate.inc:    $sql = "SELECT DISTINCT s.sid, s.value, t.tid, t.language, t.translation, t.uid_entered, t.uid_approved, t.time_entered, t.time_approved, t.has_suggestion, t.is_suggestion, t.is_active FROM {l10n_community_release} r INNER JOIN {l10n_community_file} f ON r.rid = f.rid INNER JOIN {l10n_community_line} l ON f.fid = l.fid INNER JOIN {l10n_community_string} s ON l.sid = s.sid LEFT JOIN {l10n_community_translation} t ON s.sid = t.sid AND t.language = '%s' AND t.is_active = 1 AND t.is_suggestion = 0 WHERE r.pid = %d";
l10n_community/translate.inc:    $sql_count = "SELECT COUNT(DISTINCT(s.sid)) FROM {l10n_community_release} r INNER JOIN {l10n_community_file} f ON r.rid = f.rid INNER JOIN {l10n_community_line} l ON f.fid = l.fid INNER JOIN {l10n_community_string} s ON l.sid = s.sid LEFT JOIN {l10n_community_translation} t ON s.sid = t.sid AND t.language = '%s' AND t.is_active = 1 AND t.is_suggestion = 0 WHERE r.pid = %d";
l10n_remote/l10n_remote.module:       elseif ($sid = db_result(db_query("SELECT sid FROM {l10n_community_string} WHERE value = '%s'", $source))) {

I agree that there are queries like the one before the last found, where the source table is not used but just joined through. Since the line table already has the sid, it seems unnecessary to join in the string table, so we can go directly to the translations. So instead of:

SELECT COUNT(DISTINCT(s.sid)) FROM {l10n_community_release} r INNER JOIN {l10n_community_file} f ON r.rid = f.rid INNER JOIN {l10n_community_line} l ON f.fid = l.fid INNER JOIN {l10n_community_string} s ON l.sid = s.sid LEFT JOIN {l10n_community_translation} t ON s.sid = t.sid AND t.language = '%s' AND t.is_active = 1 AND t.is_suggestion = 0 WHERE r.pid = %d

it would be:

SELECT COUNT(DISTINCT(l.sid)) FROM {l10n_community_release} r INNER JOIN {l10n_community_file} f ON r.rid = f.rid INNER JOIN {l10n_community_line} l ON f.fid = l.fid LEFT JOIN {l10n_community_translation} t ON l.sid = t.sid AND t.language = '%s' AND t.is_active = 1 AND t.is_suggestion = 0 WHERE r.pid = %d

Would be good to do a review of the above found queries and identify those that could be simplified. Marking active, since the above attached patch is committed, so no need to review it. Looking for a follow up patch.

Re #4: how would you write a join to have both the pid (from the release table) and the is_suggestion (from the translation table) available, while you omit the middle tables? They allow us to get strings related to the relevant projects! As far as I see, the most we can drop here is the "_string" table.

These queries were reviewed with great scrutiny while we introduced denormalization of project and release information and these inefficiencies are not there as far as I've seen. See #375817: Performance: denormalize releases, files and lines with project/release info