2

I have next query 

SELECT 
i.*,
gu.*
vs.*
FROM 
common.global_users gu
LEFT JOIN common.global_users_perms gup ON (gu.global_user_id=gup.global_user_id)
LEFT JOIN p.individuals i ON (gup.parent_id = i.member_id)
LEFT JOIN p.vs ON (i.member_id=vs.member_id AND vs.status IN (1,4))
WHERE gup.region = 'us'
AND gu.user_type=2
AND ((gu.email='specific@email.com') OR (i.email='specific@email.com') OR (gu.username='specific@email.com'))
ORDER BY i.status, vs.member_id;

and next plan

+----+-------------+-------+--------+------------------------------------------+------------------+---------+-----------------------------+--------+----------+---------------------------------+
| id | select_type | table | type   | possible_keys                            | key              | key_len | ref                         | rows   | filtered | Extra                           |
+----+-------------+-------+--------+------------------------------------------+------------------+---------+-----------------------------+--------+----------+---------------------------------+
|  1 | SIMPLE      | gu    | ref    | PRIMARY,username,idx_user_type,idx_email | idx_user_type    | 1       | const                       | 524243 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | gup   | ref    | global_user_id_2                         | global_user_id_2 | 4       | common.gu.global_user_id    |      1 |   100.00 | Using where                     |
|  1 | SIMPLE      | i     | eq_ref | PRIMARY                                  | PRIMARY          | 4       | common.gup.parent_id        |      1 |   100.00 | Using where                     |
|  1 | SIMPLE      |  vs   | ref    | member_id,status                         | member_id        | 4       | p.i.member_id               |      1 |   100.00 |                                 |
+----+-------------+-------+--------+------------------------------------------+------------------+---------+-----------------------------+--------+----------+---------------------------------+

Is there any possibility to make it faster ? Now it takes about 12 seconds

P.S. In gu table only two unique type values. And all number of recirds in that table more than 1M.

user1016265
  • 2,307
  • 3
  • 32
  • 49

1 Answers1

0

Your plan results might indicate the issue 'Using temporary; Using filesort' means the sort is being done on disk possibly because its to big to fit in memory. You might want to increase your memory limits if possible.

It might also be to do with the columns your sorting against. If you remove your ORDER BY does the query execute any faster? If that is the case you might want to look at ORDER BY Optimization

Mark Davidson
  • 5,503
  • 5
  • 35
  • 54
  • Yep, without ORDER clause query work faster in 2 time, BUT in my case the fields in order clause from two different tables (and no one from first table). `In some cases, MySQL cannot use indexes to resolve the ORDER BY You are joining many tables, and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows.` – user1016265 Dec 08 '12 at 13:10