here is my tests:

using having clauses:

SELECT SQL_NO_CACHE DISTINCT s.sid, s.value, s.context, t.tid, t.language, t.translation, t.uid_entered, t.time_entered, t.time_changed, t.is_suggestion, t.is_active, u.name AS username, ts.has_suggestion, ts.has_translation
FROM l10n_server_string s
LEFT JOIN l10n_server_status_flag ts ON s.sid = ts.sid
AND ts.language = 'zh-hant'
LEFT JOIN l10n_server_translation t ON ts.sid = t.sid
AND ts.language = t.language
AND t.is_active =1
LEFT JOIN users u ON u.uid = t.uid_entered
INNER JOIN l10n_server_line l ON s.sid = l.sid
WHERE l.pid =2
AND ts.has_translation =1
AND ts.has_suggestion =1
HAVING t.uid_entered =2740
LIMIT 0 , 30
Showing rows 0 - 17 ( 18 total, Query took 1.1799 sec)
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE l ref sid,pid pid 5 const 429480 Using where; Using temporary
1 SIMPLE ts eq_ref PRIMARY,sid_language_has_suggestion,sid_language_h... PRIMARY 42 l10n.l.sid,const 1 Using where
1 SIMPLE t ref sid_language_is_suggestion_is_active sid_language_is_suggestion_is_active 42 l10n.l.sid,l10n.ts.language 1
1 SIMPLE u eq_ref PRIMARY PRIMARY 4 l10n.t.uid_entered 1
1 SIMPLE s eq_ref PRIMARY PRIMARY 4 l10n.ts.sid 1 Using where

l10n_server query:

SELECT SQL_NO_CACHE DISTINCT s.sid, s.value, s.context, t.tid, t.language, t.translation, t.uid_entered, t.time_entered, t.time_changed, t.is_suggestion, t.is_active, u.name AS username, ts.has_suggestion, ts.has_translation
FROM l10n_server_string s
LEFT JOIN l10n_server_status_flag ts ON s.sid = ts.sid
AND ts.language = 'zh-hant'
LEFT JOIN l10n_server_translation t ON ts.sid = t.sid
AND ts.language = t.language
AND t.is_active =1
LEFT JOIN users u ON u.uid = t.uid_entered
INNER JOIN l10n_server_line l ON s.sid = l.sid
WHERE t.uid_entered =2740
AND l.pid =2
AND ts.has_translation =1
AND ts.has_suggestion =1
LIMIT 0 , 30
Showing rows 0 - 17 ( 18 total, Query took 33.8020 sec)
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t ref uid_entered,sid_language_is_suggestion_is_active uid_entered 4 const 6199 Using where; Using temporary
1 SIMPLE u const PRIMARY PRIMARY 4 const 1
1 SIMPLE s eq_ref PRIMARY PRIMARY 4 l10n.t.sid 1
1 SIMPLE l ref sid,pid sid 5 l10n.s.sid 10 Using where
1 SIMPLE ts eq_ref PRIMARY,sid_language_has_suggestion,sid_language_h... PRIMARY 42 l10n.s.sid,const 1 Using where

Comments

droplet’s picture

droplet commented
Status: Active » Postponed