php - Optimization of mysql query with multiple JOINs -

i building mysql query access videos according access rights, 1 come takes 0.5s execute on our server.

the access rights rather flexible, bit complex. tables:

vod_reunion contains 1 line per video relevant columns are: id (unique identifier) , "active"

each video belongs 1 or more categories per following table:

vod_appart_droit id_theme (id of categorie) id_reunion (id of video, matching 1 of first table)

each user can belong groups:

appartenance_groupe defines group belong user: id_groupe id_membre actual rights:

vod_droit: id_groupe: id of group if record managing access group, otherwise "-1" id_membre: id of user if record managing access directly user, otherwise "-1" id_theme: id of category user or group may access droit: access level groupe or user category values: 2: access allowed 1: access allowed videos of category having flag "active" set @ 1 in first table "vod_reunion" 0: no access category -1: forbids access videos of category user or group, if same video belongs other category same group has access.

so here query:

select      distinct r.*, vu.vu vod_reunion r left join     (         vod_appart_droit ad     inner join         vod_droit vd         on         ad.id_theme = vd.id_theme ,          (vd.droit =2 or vd.droit=1)     inner join         appartenance_groupe ag         on         vd.id_groupe = ag.id_groupe ,         ag.id_membre=11     )     on     r.id = ad.id_reunion ,      (vd.droit =2 or (vd.droit=1 , r.active=1))       left join     (         vod_appart_droit adn     inner join         vod_droit vnd         on         adn.id_theme = vnd.id_theme ,          vnd.droit =-1     inner join         appartenance_groupe ang         on         vnd.id_groupe = ang.id_groupe ,         ang.id_membre=11       )     on     r.id = adn.id_reunion ,     ag.id_groupe = ang.id_groupe       left join     (         vod_appart_droit adm     inner join         vod_droit vdm         on         adm.id_theme = vdm.id_theme ,          (vdm.droit =2 or vdm.droit=1) ,         vdm.id_membre=11     )     on     r.id = adm.id_reunion ,      (vdm.droit =2 or (vdm.droit=1 , r.active=1))      left join     (         vod_appart_droit adnm     inner join         vod_droit vndm         on         adnm.id_theme = vndm.id_theme ,          vndm.droit =-1 ,         vndm.id_membre=11     )     on     r.id = adnm.id_reunion      left join      vod_vu vu on r.id=vu.id_reunion , vu.id_membre='11'      (r.langue = 0 or r.langue = 2) ,     ((vd.droit not null , vnd.droit null) or     (vdm.droit not null , vndm.droit null)) 

note clause simplified here, left parts related access right, there multiple criteria used searching & sorting.

would have on how accelerate query ? note when run multiple times consecutively can go extremely fast, guess due caching capabilities of mysql.

thank timothé

this long comment.

first, remove distinct if not necessary.

second, write where clause r.langue in (0, 2) , sure there index on column.

third, if need distinct, remove joins. instead, use not exists in where clause.

these ideas might performance. assume obvious columns correctly indexed (the columns used in joins instance).