The left joins making query slow,is there any method to increase the speed of this query

select 
    b.entry_id,
    b.assign_id,
    a.profile_type,
    a.profile_id,
    a.profile_name,
    a.profile_status,
    b.entry_type,
    b.assign_id,
    c.chapter_name,
    d.section_name,
    h.group_name,
    i.programme_name,
    k.subjectprogramme_name,
    j.masterprogramme_name,
    l.developmentprogramme_name
from profile_master a
left join profile_assign b on (a.profile_id = b.profile_id)  
left join chapter_master c 
       on (b.entry_id = c.chapter_id and b.entry_type='chapter') 
left join section_master d 
       on (b.entry_id = d.section_id and b.entry_type='section')
left join group_master h 
       on (b.entry_id = h.group_id and b.entry_type='Group' 
           and h.year_id='".$this->year."')
left join programme_master i 
       on (b.entry_id = i.programme_id and b.entry_type='Programme' 
           and i.year_id='".$this->year."')
left join subjectprogramme_master k 
       on (b.entry_id = k.subjectprogramme_id and b.entry_type='subjectProgramme' 
           and k.year_id='".$this->year."')
left join masterprogramme_master j 
       on (b.entry_id = j.masterprogramme_id and b.entry_type='masterProgramme' 
           and j.year_id='".$this->year."')
left join developmentprogramme_master l 
       on (b.entry_id = l.de开发者_C百科velopmentprogramme_id 
           and b.entry_type='developmentProgramme')

---

1) Get rid of where coditions from left join. Use WHERE clause for filtering

2) I guess UNION or 7 queries (by each entity separetely) will be much better in your case

---

This is a hard question to answer without having direct access to the database, so I'll try a general answer!

• Use "explain" on this query to see if MySQL suggests some indexes. No doubt it'll suggest a few, because you're accessing a few columns several times, and oftentimes indexes will improve even the slowest OUTER JOIN
• You're using lots of checks against $this->year, so that would suggest some composite indexes where e.g. the programme_id and the year_id are both in the same index

Of course, there are solutions that might depend on how you're using the output, e.g.:

• If this query is run frequently enough to be a problem for users waiting for it, but infrequently enough for latency not to be an issue (e.g. it's ok to run it based on last night's data), you could run it overnight and cache the results.

---

You really only do a join when a condition is passed, I suggest doing subselects like so:

SELECT 
    b.entry_id, 
    b.assign_id, 
    a.profile_type, 
    a.profile_id, 
    a.profile_name, 
    a.profile_status, 
    b.entry_type, 
    b.assign_id, 
    CASE b.entry_type 
    WHEN 'chapter' THEN SELECT(c.chapter_name FROM c WHERE b.entry_id = c.chapter_id)
    WHEN 'section' THEN SELECT(d.section_name FROM d WHERE b.entry_id = d.section_id)
    WHEN ....
    END as name
from profile_master a 
left join profile_assign b on (a.profile_id = b.profile_id)   

If you insist on having the output be the same, then you need to wrap this select in a outer select like so:

SELECT 
  entry_id, assign_id, ......
  , CASE entry_type WHEN 'chapter' THEN name ELSE null END as chapter_name
  , CASE entry_type WHEN 'section' THEN name ELSE null END as section_name
FROM
  (select statement like above) sub