MySQL Forums
Forum List  »  Optimizer & Parser

Re: Help with query/tables optimization
Posted by: Rick James
Date: April 16, 2012 12:05AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
2176
April 14, 2012 05:20AM
Re: Help with query/tables optimization
1300
April 16, 2012 12:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.