MySQL Forums
Forum List  »  Optimizer & Parser

Re: Help with query/tables optimization
Posted by: Oleg Ivanov
Date: April 16, 2012 11:53AM

Rick, you are amazing! Results from you suggestions are very good. I understood the idea and can continue working on other queries.

Here is what I did step by step and the results:

1. Run initial query on my laptop (Intel i5, 2.4 GHz, 8 GB RAM)

Query took 1.4779 sec
Query took 1.2192 sec
Query took 1.2205 sec
Query took 1.4778 sec

2. Replace
AND ( (up.lang = 'et' AND up.accepted = 1) OR (up.lang = 'en' AND up.accepted = 1) OR (up.lang = 'ru' AND up.accepted = 1) )

by

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

Query took 1.2122 sec
Query took 1.2558 sec
Query took 1.2204 sec

3. INDEX(parent_lang)

Query took 1.0088 sec
Query took 1.0240 sec
Query took 0.6540 sec
Query took 0.6357 sec

4. INDEX(lang, parent_lang)

Query took 0.6165 sec
Query took 0.6141 sec
Query took 0.9543 sec
Query took 0.6137 sec

5. Drop PRIMARY key from artificial id; ADD PRIMARY KEY ( `parent_lang` , `lang` ); ADD UNIQUE `id` ( `id` );

Query took 0.3244 sec
Query took 0.2886 sec
Query took 0.3329 sec
Query took 0.3129 sec
Query took 0.2904 sec

6. `user_profiles` ADD INDEX `accepted_lang` ( `accepted` , `lang` )

Query took 0.3032 sec
Query took 0.3737 sec
Query took 0.2901 sec
Query took 0.2949 sec
Query took 0.2899 sec

I also changed int(2), int(5) to appropriate types (tinyint, smallint).

Thanks a lot once again :)

Options: ReplyQuote


Subject
Views
Written By
Posted
2196
April 14, 2012 05:20AM
Re: Help with query/tables optimization
1392
April 16, 2012 11:53AM


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.