Re: Help with query/tables optimization

[Rick James]

AND ( (up.lang = 'et' AND up.accepted = 1)
OR (up.lang = 'en' AND up.accepted = 1)
OR (up.lang = 'ru' AND up.accepted = 1) )
-->
AND up.accepted = 1
AND up.lang IN ('et', 'en', 'ru')

Then learn about "INDEXing".

> JOIN translation_languages_translation AS tl1 ON tl1.parent_lang = tsl.language_from
-- translation_languages_translation probably could benefit from
INDEX(parent_lang)
The SELECT is a bit confusing to me, so add this compound index, too:
INDEX(lang, parent_lang)

Seems like you want lang instead of parent_lang.

Is `lang` or `parent_lang` unique in translation_languages_translation? If so, then make it the PRIMARY KEY instead of the artificial id.

user_profiles might benefit from
INDEX(accepted, lang)
(after rewriting as suggested above)

Strange -- you are using TINYINT, yet you say INT(5). Look up SMALLINT.

If you still have trouble after those changes, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes (after revisions)
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
How much RAM do you have?