Re: Help with query/tables optimization

[Oleg Ivanov]

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 :)