MySQL query optimization

Question

Just wondering what's a better way to write this query. Cheers.

SELECT r.user_id AS ID, m.prenom, m.nom
FROM `0_rank` AS l
LEFT JOIN `0_right` AS r ON r.rank_id = l.id
LEFT JOIN `0_user` AS m ON r.user_id = m.id
WHERE r.section_id = $section_id
AND l.rank = '$rank_name' AND depart_id IN 
(SELECT depart_id FROM 0_depart WHERE user_id = $user_id AND section_id = $section_id) 
GROUP BY r.user_id

Here are the table structures:

0_rank: id | section_id | rank_name | other_stuffs
0_user: id | prenom | nom | other_stuffs
0_right: id | section_id | user_id | rank_id | other_stuffs
0_depart: id | section_id | user_id | depart_id | other_stuffs

The idea is to use the same in a function like:

public function usergroup($section_id,$rank_name,$user_id) { 
 // mysql query goes here to get a list of appropriate users
}

Update: I think I have not been able to express myself clearly earlier. Here is the most recent query that seems to be working.

SELECT m.id, m.prenom, m.nom, 
CAST( GROUP_CONCAT( DISTINCT d.depart ) AS char ) AS deps, 
CAST( GROUP_CONCAT( DISTINCT x.depart ) AS char ) AS depx
FROM `0_rank` AS l
LEFT JOIN `0_right` AS r ON r.rank_id = l.id
LEFT JOIN `0_member` AS m ON r.user_id = m.id
LEFT JOIN `0_depart` AS d ON m.id = d.user_id
LEFT JOIN `0_depart` AS x ON x.user_id = $user_id
WHERE r.section = $section_id
AND l.rank = '$rank_name'
GROUP BY r.user_id ORDER BY prenom, nom

Now I want to get only those result, where all entries of deps are present in entries in depx.

In other term, every user is associated with some departs. $user_id is also an user is associated with some departs.

I want to get those users whose departs are common to the departs of $user_id.

Cheers.

Can you show us what the relavant tables looks like? There might be something that can be changed in the way the tables are structured to make everything faster. — rzetterberg
– rzetterberg, Commented Apr 10, 2011 at 11:33

Hogan · Accepted Answer · 2011-04-11 15:48:53Z

1

Update

I'm not sure without being able to see the data but I believe this query will give you the results you want the fastest.

SELECT m.id, m.prenom, m.nom, 
CAST( GROUP_CONCAT( DISTINCT d.depart ) AS char ) AS deps, 
FROM `0_rank` AS l
LEFT JOIN `0_right` AS r ON r.rank_id = l.id and r.user_id = $user_id
LEFT JOIN `0_member` AS m ON r.user_id = m.id
LEFT JOIN `0_depart` AS d ON m.id = d.user_id
WHERE r.section = $section_id
AND l.rank = '$rank_name'
GROUP BY r.user_id ORDER BY prenom, nom

Let me know if this works.

Try this: (By converting the functionality of the IN (SELECT...) to an inner join, you get exactly the same results but it might be the optimizer will make better choices.)

SELECT r.user_id AS ID, m.prenom, m.nom
FROM `0_rank` AS l
LEFT JOIN `0_right` AS r ON r.rank_id = l.id and r.section_id = 2
LEFT JOIN `0_user` AS m ON r.user_id = m.id
INNER JOIN `0_depart` AS x ON l.section_id = x.section_id and x.user_id = $user_id AND x.section_id = $section_id
WHERE l.rank = 'mod' 
GROUP BY r.user_id

I also moved the constraints on 0_right to the join statement because I think that is clearer -- presumably this change won't matter to the optimizer.

edited Apr 11, 2011 at 15:48

answered Apr 10, 2011 at 13:10

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jeremy Roy Over a year ago

Thanks @Hogan. Here, I don't have l.depart_id.

Jeremy Roy Over a year ago

Thanks @Hogan, but it's not returning the expected result. I'm getting zero rows. :(

Jeremy Roy Over a year ago

@Hogan, I have updated the current query, while to be clearer. I also added +1 to your post, thanking to your efforts.

Álvaro González · Accepted Answer · 2011-04-10 12:31:59Z

1

I know nothing about your DB structure but your subselect looks like it can be replaced with a simple INNER JOIN against whatever table has the depart column. MySQL is well known for its poor subquery optimization.

answered Apr 10, 2011 at 12:31

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Comments

DRapp · Accepted Answer · 2011-04-10 14:53:47Z

1

Without knowing the structures or indexes, I would first add "STRAIGHT_JOIN" if the critical criteria is in-fact from the 0-rank table. Then, ensure 0_rank has an index on "rank". Next, ensure the 0_right has an index on rank_id at a minimum, but rank_id, section to take advantage of BOTH your criteria. Index on 0_member on id.

Additionally, do you mean left-join (ie: record only required in the 0_rank or 0_member) on the respective 0_right and 0_member tables instead of a normal join (where BOTH tables must match on their IDs).

Finally, ensure index on the depart table on user_id.

SELECT STRAIGHT_JOIN 
        r.user_id AS ID, 
        m.prenom, 
        m.nom
    FROM 
        0_rank AS l
            LEFT JOIN `0_right` AS r 
                ON l.id = r.rank_id 
                AND r.section = 2
            LEFT JOIN `0_member` AS m 
                ON r.user_id = m.id
    WHERE 
          l.rank = 'mod' 
      AND depart IN (SELECT depart 
                       FROM 0_depart 
                      WHERE user_id = 2 
                        AND user_sec = 2) 
    GROUP BY 
       r.user_id

---- revised post from feedback. From the parameters you are listing, you are always including the User ID... If so, I would completely restructure it to get whatever info is for that user. Each user should apparently can be associated to multiple departments and may or may NOT match the given rank / department / section you are looking for... I would START the query with the ONE USER because THAT will guarantee a single entry, THEN tune-down to the other elements...

select STRAIGHT_JOIN
        u.id,
        u.prenom,
        u.nom,
        u.other_stuffs,
        rank.rank_name
    from 
        0_user u

            left join 0_right r
                 on u.id = r.user_id
                AND r.section_id = $section_id

                join 0_rank rank
                    on r.rank_id = rank.id
                    AND rank.rank_name = '$rank_name'

            left join 0_dept dept
                on u.id = dept.user_id
    where 
        u.id = $user_id

Additionally, I have concern about your table relationships and don't see a legit join to the department table...

0_user
   0_right by User_ID
      0_rank by right.rank_id


0_dept has section which could join to rank or right, but nothing to user_id directly

edited Apr 10, 2011 at 14:53

answered Apr 10, 2011 at 12:35

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

6 Comments

Jeremy Roy Over a year ago

@DRapp, thanks for the detailed reply. I've updated my original post with table structure and more meaningful column name.

Jeremy Roy Over a year ago

Thanks @DRapp, but it's not returning the expected result. I'm getting zero rows. :(

DRapp Over a year ago

@Jeremy Roy, Updated per structure, but have concerns too... see notes at bottom.

Jeremy Roy Over a year ago

Thanks @DRapp, I have updated, there is a user_id col in department table, I just forgot to put that.

DRapp Over a year ago

@Jeremy Roy, updated with last join to dept table... This should get what you want... If you had any additional criteria, I would just add to the JOIN condition to the related tables, or to the WHERE if its directly against the user's table.

|

David Rabinowitz · Accepted Answer · 2011-04-10 12:21:57Z

0

Run explain on the query - it will help you find where the caveats are:

EXPLAIN SELECT r.user_id AS ID, m.prenom, m.nom
FROM 0_rank AS l
LEFT JOIN `0_right` AS r ON r.rank_id = l.id
LEFT JOIN `0_member` AS m ON r.user_id = m.id
WHERE r.section = 2
AND l.rank = 'mod' AND depart IN 
(SELECT depart FROM 0_depart WHERE user_id = 2 AND user_sec = 2) 
GROUP BY r.user_id\G

answered Apr 10, 2011 at 12:21

David Rabinowitz

30.5k16 gold badges95 silver badges125 bronze badges

Collectives™ on Stack Overflow

MySQL query optimization

4 Answers 4

3 Comments

Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related