mysql case join,使用CASE进行MYSQL LEFT JOIN优化

I spent some time trying to get working this SELECT with CASE but I failed... (thank to that I'm using COLASCE() now)

How could I optimize this SELECT by using CASE/IF sentences? Is this a fast way to query from different tables selected by a field?

SELECT a.folderid, a.foldername, a.contenttype, COALESCE(b.descriptor, c.descriptor, d.descriptor, e.descriptor, f.descriptor) as descriptor

FROM t_folders a

LEFT JOIN t_files b

ON a.contenttype = 'file' AND a.contentid = b.fileid

LEFT JOIN t_links c

ON a.contenttype = 'link' AND a.contentid = c.linkid

LEFT JOIN t_extfiles d

ON a.contenttype = 'extfile' AND a.contentid = d.extfileid

LEFT JOIN t_videos e

ON a.contenttype = 'video' AND a.contentid = e.videoid

LEFT JOIN t_exams f

ON a.contenttype = 'exam' AND a.contentid = f.examid

WHERE a.folderid = $folderId

ORDER BY a.folderid DESC

解决方案

Using case statement will not make the query faster in your case, but since you asked for it, below is how it would look like.

SELECT a.folderid, a.foldername, a.contenttype,

(CASE a.contenttype

WHEN 'file' THEN b.descriptor

WHEN 'link' THEN c.descriptor

WHEN 'extfile' THEN d.descriptor

WHEN 'video' THEN e.descriptor

ELSE f.descriptor

END CASE) AS descriptor

FROM t_folders a

LEFT JOIN t_files b ON a.contenttype = 'file' AND a.contentid = b.fileid

LEFT JOIN t_links c ON a.contenttype = 'link' AND a.contentid = c.linkid

LEFT JOIN t_extfiles d ON a.contenttype = 'extfile' AND a.contentid = d.extfileid

LEFT JOIN t_videos e ON a.contenttype = 'video' AND a.contentid = e.videoid

LEFT JOIN t_exams f ON a.contenttype = 'exam' AND a.contentid = f.examid

WHERE a.folderid = $folderId

ORDER BY a.folderid DESC

If each of the t_files, t_links, etc tables has the folder_id field, I would also try doing a UNION on these tables and then left join the result with t_folders to get the folderid and foldername.