SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot Pozor's Avatar
    Join Date
    Apr 2004
    Location
    Switzerland
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple table query

    Hi,

    I want to get all menu items, with their associated translated text, which the current user is alowed to see. This is either by rights or roles he possess. He get additionally all blind ones and all from the role 1 and 3.

    My Problem is this just dont work, the DB doesn't response, not even an error. It seems it takes just too long to get the results from this query.

    There are 3 normal tables and 4 many2many tables involved. The many2many
    tables have all an Primary index over the 2 Col.

    Code:
                          SELECT DISTINCT
                          m.tb_m_pk_id		AS menu_id,
                          m.tb_m_fk_id_top	AS menu_topid,
                          m.tb_m_fk_id_parent AS menu_parentid,
                          m.tb_m_link		 AS menu_link,
                          mt.tb_mt_text	   AS menu_name,
                          m.tb_m_level		AS menu_level,
                          m.tb_m_location	 AS menu_location
                          
                          FROM 
                          tb_menu AS m LEFT JOIN tb_menutext AS mt ON
                          (m.tb_m_pk_id = mt.tb_m_fk_id AND mt.tb_l_fk_id = 1),
                          
                          cl_userright  AS cl_ur, 
                          cl_userrole   AS cl_ug, 
                          cl_roleright  AS cl_gr,
                          ac_right	  AS r,
                          
                          cl_menuright  AS cl_mr
                          
                          WHERE 
                          cl_ur.ac_u_fk_id = 2		AND
                          cl_ur.ac_r_fk_id = r.ac_r_pk_id	 AND
                          cl_ur.ac_r_fk_id = cl_mr.ac_r_fk_id AND
                          cl_mr.tb_m_fk_id = m.tb_m_pk_id
                          
                          OR
                          cl_ug.ac_u_fk_id  = 2		  AND
                          cl_ug.ac_r_fk_id  = cl_gr.fk_role_id   AND
                          r.ac_r_pk_id	  = cl_gr.fk_right_id  AND
                          cl_gr.fk_right_id = cl_mr.ac_r_fk_id   AND
                          cl_mr.tb_m_fk_id  = m.tb_m_pk_id
                          	
                          OR
                          m.tb_m_blind = 1
                          
                          OR
                          cl_gr.fk_role_id  IN(1,3)			 AND   
                          r.ac_r_pk_id	  = cl_gr.fk_right_id AND   
                          r.ac_r_pk_id	  = cl_mr.ac_r_fk_id  AND
                          cl_mr.tb_m_fk_id  = m.tb_m_pk_id
                          
                          ORDER BY 
                          m.tb_m_top_order ASC, 
                          m.tb_m_level DESC, 
                          m.tb_m_order ASC
    Then i tried an other one, with subselect which selects first all rights.
    It works but it is very slow, it takes about 25 sec.
    The subquery alone takes about 0.0115 sec.
    Code:
    SELECT DISTINCT
                         m.tb_m_pk_id		AS menu_id,
                         m.tb_m_fk_id_top	AS menu_topid,
                         m.tb_m_fk_id_parent AS menu_parentid,
                         m.tb_m_link		 AS menu_link,
                         mt.tb_mt_text	   AS menu_name,
                         m.tb_m_level		AS menu_level,
                         m.tb_m_location	 AS menu_location
                         
                         FROM 
                         tb_menu AS m LEFT JOIN tb_menutext AS mt ON
                         (m.tb_m_pk_id = mt.tb_m_fk_id AND mt.tb_l_fk_id = 1),
                         cl_menuright  AS cl_mr
                         
                         WHERE 
                         cl_mr.ac_r_fk_id IN
                         (
                         SELECT DISTINCT
                         r.ac_r_pk_id		AS right_id
                         
                         FROM 
                         cl_userright  AS cl_ur, 
                         cl_userrole   AS cl_ug, 
                         cl_roleright  AS cl_gr,
                         ac_right	  AS r
                         
                         WHERE 
                         cl_ur.ac_u_fk_id = 2				AND
                         cl_ur.ac_r_fk_id = r.ac_r_pk_id
                         
                         OR
                         cl_ug.ac_u_fk_id  = 2				  AND
                         cl_ug.ac_r_fk_id  = cl_gr.fk_role_id   AND
                         r.ac_r_pk_id	  = cl_gr.fk_right_id
                         	
                         OR
                         cl_gr.fk_role_id  IN(1,3)			 AND   
                         r.ac_r_pk_id	  = cl_gr.fk_right_id 
                         ) AND
                         cl_mr.tb_m_fk_id = m.tb_m_pk_id
                         
                         OR
                         m.tb_m_blind = 1
                         
                         ORDER BY 
                         m.tb_m_top_order ASC, 
                         m.tb_m_level DESC, 
                         m.tb_m_order ASC
    I tried EXPLAIN but i dont know what i really means.

    Code:
    id..select_type.........table.....type....possible_keys...key.......key_len...ref...rows...Extra
       1...PRIMARY.............m.........ALL.....PRIMARY.........NULL......NULL......NULL..67.....Using.temporary;.Using.filesort
       1...PRIMARY.............mt........ALL.....NULL............NULL......NULL......NULL..201...
       1...PRIMARY.............cl_mr.....index...PRIMARY.........PRIMARY...8.........NULL..251.....Using.where;.Using.index
       2...DEPENDENT.SUBQUERY..cl_ur.....index...PRIMARY.........PRIMARY...8.........NULL..5.......Using.index;.Using.temporary
       2...DEPENDENT.SUBQUERY..cl_ug.....index...PRIMARY.........PRIMARY...8.........NULL..4.......Using.index
       2...DEPENDENT.SUBQUERY..cl_gr.....index...PRIMARY.........PRIMARY...8.........NULL..76......Using.where;.Using.index
       2...DEPENDENT.SUBQUERY..r.........eq_ref..PRIMARY.........PRIMARY...4.........func..1.......Using.where;.Using.index
    Why is my query dosnt working well, are they just to many tables (7), is mysql not suited for that?
    Or is my query just a good piece for optimisation? Should i try to make 2 queries out of them (just because the subquery is way faster then the hole thing).
    The index (primary) on the many2many tables can be done better?

    greets Stefan

  2. #2
    SitePoint Zealot Pozor's Avatar
    Join Date
    Apr 2004
    Location
    Switzerland
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've tried to execute it in two queries and it is way faster. But why?
    i've executed the subselect query by it own and used the result to build the main one.
    The subselect needs still 0.0115sec and the new main one now only 0,0297sec.
    Its way faster.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i have several suggestions --

    first, try to write your query using only JOIN syntax, don't mix JOIN syntax with "comma-list" syntax

    don't use DISTINCT to mask query problems

    if you have situations where sometimes a row should be joined to this row, and sometimes a row should be joined to that row, break the query up into two separate queries and combine the results with UNION

    here's what i ended up with after i assigned your nefarious WHERE conditions to the ON clauses of JOINs --
    Code:
    SELECT m.tb_m_pk_id		AS menu_id
         , m.tb_m_fk_id_top	AS menu_topid
         , m.tb_m_fk_id_parent AS menu_parentid
         , m.tb_m_link		 AS menu_link
         , mt.tb_mt_text	   AS menu_name
         , m.tb_m_level		AS menu_level
         , m.tb_m_location	 AS menu_location
      FROM tb_menu AS m 
    inner
      join cl_userright  AS cl_ur
        on cl_ur.ac_u_fk_id = 2
    inner
      join cl_userrole   AS cl_ug
        on cl_ug.ac_u_fk_id  = 2
    inner
      join cl_roleright  AS cl_gr
        on cl_gr.fk_role_id = cl_ug.ac_r_fk_id  
        or cl_gr.fk_right_id = cl_mr.ac_r_fk_id
        or cl_gr.fk_role_id  IN(1,3)
    inner
      join ac_right	  AS r
        on r.ac_r_pk_id = cl_ur.ac_r_fk_id 
        or r.ac_r_pk_id	= cl_gr.fk_right_id       
    inner
      join cl_menuright  AS cl_mr
        on cl_mr.ac_r_fk_id = cl_ur.ac_r_fk_id
        or cl_mr.ac_r_fk_id = r.ac_r_pk_id
        or cl_mr.tb_m_fk_id = m.tb_m_pk_id
    LEFT 
      JOIN tb_menutext AS mt 
        ON mt.tb_m_fk_id = m.tb_m_pk_i
       AND mt.tb_l_fk_id = 1
     WHERE m.tb_m_blind = 1
    ORDER 
        BY m.tb_m_top_order ASC
         , m.tb_m_level DESC
         , m.tb_m_order ASC
    you can see that the ON clauses in the first two joins will result in sever cross join effects which is probably why you needed the DISTINCT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot Pozor's Avatar
    Join Date
    Apr 2004
    Location
    Switzerland
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I've tried that, and it had the same bad performance (25sec) as the subselect try and the
    thing with the i had to remove to get not only "blind" rows.

    What the query should do is just to get all menuitems with their related translation.
    I seems that one query isnt the right solution.

    Do you have an idea why it is that slow? The subselect is runned just once, isn't it?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i don't know why the subquery is slow, i can only guess -- it's returning far too many rows

    if you wish to see, run the subquery by itself, without DISTINCT

    did you understand what i meant by breaking up the query into several queries that are combined with UNION?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot Pozor's Avatar
    Join Date
    Apr 2004
    Location
    Switzerland
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    i solved it with the union syntax, it is 3times faster than the splitted queries.
    Thanks a lot, SQL is a topic i still have to learn a lot. I wasnt aware of the union
    thing before.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •