Explain SQL and Query optimization

Question

Explain SQL

Explain SQL (in phpmyadmin) of a query that is taking more than 5 seconds is giving me the above. I read that we can study the Explain SQL to optimize a query. Can anyone tell if this Explain SQL telling anything as such?

Thanks guys.

Edit:

The query itself:

SELECT 
a.`depart` , a.user, 
m.civ, m.prenom, m.nom, 
CAST( GROUP_CONCAT( DISTINCT concat( c.id, '~', c.prenom, ' ', c.nom ) ) AS char ) AS coordinateur, 
z.dr
FROM `0_activite` AS a
JOIN `0_member` AS m ON a.user = m.id
LEFT JOIN `0_depart` AS d ON ( m.depart = d.depart AND d.rank = 'mod' AND d.user_sec =2 )
LEFT JOIN `0_member` AS c ON d.user_id = c.id
LEFT JOIN `zone_base` AS z ON m.depart = z.deprt_num
GROUP BY a.user

Edit 2:

Structures of the two tables a and d Table structures . Top: a and bottom: d

Edit 3:

What I want in this query?

I first want to get the value of 'depart' and 'user' (which is an id) from the table 0_activite. Next, I want to get name of the person (civ, prenom and name) from 0_member whose id I am getting from 0_activite via 'user', by matching 0_activite.user with 0_member.id. Here depart is short of department which is also an id.

So at this point, I have depart, id, civ, nom and prenom of a person from two tables, 0_activite and 0_member.

Next, I want to know which dr is related with this depart, and this I get from zone_base. The value of depart is same in both 0_activite and 0_member.

Then comes the trickier part. A person from 0_member can be associated with multiple departs and this is stored in 0_depart. Also, every user has a level, one of what is 'mod', stands for moderator. Now I want to get all the people who are moderators in the depart from where the first user is, and then get those moderaor's name from 0_member again. I also have a variable user_sec, but this is probably less important in this context, though I cannot overlook it.

This is what makes the query a tricky one. 0_member is storing id, name of users, + one depart, 0_depart is storing all departs of users, one line for each depart, and 0_activite is storing some other stuffs and I want to relate those through userid of 0_activite and the rest.

Hope I have been clear. If I am not, please let me know and I will try again to edit this post.

Many many thanks again.

You will get very good explanation as soon as you show the table structure(with datatype) and the query — Ibu
– Ibu, Commented May 30, 2011 at 22:34
@Jeremy - thanks, that should assist greatly. How many results do you get from this query? — Will A
– Will A, Commented May 30, 2011 at 22:52
Hi Jeremy, from your latest edits. let me paraphrase what I think you want. For a given user... who is associated with multiple departments, you want to know who the moderator is OF those departments (in addition to the starting user)... hence you are joining to the user table twice... once for the starting person, second to get name info for who the moderators are. Am I very close? — DRapp
– DRapp, Commented May 31, 2011 at 21:46
well @DRapp, I have actually simplifying this a bit. the depart value stored in 0_member is the main depart of an user, additional departs are (which may or may not be present) are auxiliary departs. While looking for the moderator of an user, only the main department of the user is getting into consideration. So, user A has depart aa, user B has depart bb, user C has depart cc as main departs. Mod B has depart aa as main and cc as auxiliary depart. Here, we will see mod B for both user A and user C. — Jeremy Roy
– Jeremy Roy, Commented May 31, 2011 at 22:09

DRapp · Accepted Answer · 2011-06-01 00:42:53Z

2

Aside from the few answers provided by the others here, it might help to better understand the "what do I want" from the query. As you've accepted a rather recent answer from me in another of your questions, you have filters applied by department information.

Your query is doing a LEFT join at the Department table by rank = 'mod' and user_sec = 2. Is your overall intent to show ALL records in the 0_activite table REGARDLESS of a valid join to the 0_Depart table... and if there IS a match to the 0_Depart table, you only care about the 'mod' and 2 values?

If you only care about those people specifically associated with the 0_depart with 'mod' and 2 conditions, I would reverse the query starting with THIS table first, then join to the rest.

Having keys on tables via relationship or criteria is always a performance benefit (vs not having the indexes).

Start your query with whatever would be your smallest set FIRST, then join to other tables.

From clarification in your question... I would start with the inner-most... Who it is and what departments are they associated with... THEN get the moderators (from department where condition)... Then get actual moderator's name info... and finally out to your zone_base for the dr based on the department of the MODERATOR...

select STRAIGHT_JOIN
      DeptPerMember.*
      Moderator.Civ as ModCiv,
      Moderator.Prenom as ModPrenom,
      Moderator.Nom as ModNom,
      z.dr
   from 
      ( select
              m.ID,
              m.Depart,
              m.Civ,
              m.Prenom,
              m.Nom
           from
              0_Activite as a
                 join 0_member m
                    on a.User = m.ID
                    join 0_Depart as d
                       on m.depart = d.depart  ) DeptPerMember

      join 0_Depart as DeptForMod
         on DeptPerMember.Depart = DeptForMod.Depart
         and DeptForMod.rank = 'mod'
         and DeptForMod.user_sec = 2 

         join 0_Member as Moderator
            on DeptForMod.user_id = Moderator.ID

            join zone_base z
               on Moderator.depart = z.deprt_num

Notice how I tier'd the query to get each part and joined to the next and next and next. I'm building the chain based on the results of the previous with clear "alias" references for clarification of content. Now, you can get whatever respective elements from any of the levels via their distinct "alias" references...

edited Jun 1, 2011 at 0:42

answered May 31, 2011 at 0:12

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

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

4 Comments

Jeremy Roy Over a year ago

Thanks @DRapp, I really appreciated your help. I tried to explain what I want by editing the post above, let me know how do you see that. Thanks again.

Jeremy Roy Over a year ago

Many thanks @DRapp. isn't this method has, unless I am missing something, a problem, like: the table 0_activite is listing something, activities. Now not all members will have activities. I was taking the 0_activite table first as to see which users has activities, and then, to get related information about that user (like name, moderator's name, dr, etc). What do you think here please? Also, unless I need, I can remove where m.ID = AnIDIfYouWantOnlyOnePerson, isn't it?

DRapp Over a year ago

@Jeremy Roy, sorry delay, outside working... I didn't realize exactly what your Activite table was... Yes, I've moved that to the first position, then joined to member and finally to depart.

Jeremy Roy Over a year ago

many many thanks @DRapp. This is indeed wonderful. Great work. :)

Will A · Accepted Answer · 2011-05-30 23:01:20Z

2

The output from EXPLAIN is showing us that the first and third tables listed (a & d) are not having any indexes utilised by the database engine in executing this query. The key column is NULL for both - which is a shame since both are 'large' tables (OK, they're not really large, but compared to the rest of the tables they're the big 'uns).

Judging from the query, an index on user on 0_activite and an index on (depart, rank, user_sec) on 0_depart would go some way to improving performance.

answered May 30, 2011 at 23:01

Will A

25k5 gold badges54 silver badges61 bronze badges

3 Comments

Jeremy Roy Over a year ago

Thanks @will A. Well, it's strange because I do have indexes in those cols. probably I didn't put the indexes in the 'right' way? I edited the original post to add new images.

Will A Over a year ago

No problem Jeremy. Your index on 0_depart isn't (depart, rank, user_sec) - I think this index would be worth a try.

Jeremy Roy Over a year ago

do you mean an index combining these 3 @Will ?

wired00 · Accepted Answer · 2011-05-30 23:11:43Z

2

you can see that columns key and key_len are null this means its not using any key in the possible_keys column. So table a and d are both scanning all rows. (check larger numbers in rows column. you want this smaller).

To deal with 0_depart: Make sure you have a key on (d.depart, d.rank,d.user_sec) which are part of the join of 0_depart.

To deal with 0_activite: I'm not positive but a GROUP column should be indexed too so you need a key on a.user

edited May 30, 2011 at 23:11

answered May 30, 2011 at 23:02

wired00

14.6k9 gold badges76 silver badges78 bronze badges

1 Comment

Jeremy Roy Over a year ago

Thanks @weired00. Well, it's strange because I do have indexes in those cols. probably I didn't put the indexes in the 'right' way? I edited the original post to add new images.

Collectives™ on Stack Overflow

Explain SQL and Query optimization

3 Answers 3

4 Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related