Optimizing MYSQL LEFT JOIN with CASE
I spent some time working with this SELECT with CASE, but I failed ... (thanks to the fact that I am using COLASCE () now)
How can I optimize this SELECT with CASE / IF clauses? Is this a fast way to query from different tables selected by the 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
source to share
Using a case statement won't make the query faster in your case, but since you asked for it, the following is how it would look.
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 and etc tables has a folder_id field, I will also try doing UNION on those tables and then curing the result with t_folders to get the folder and folder name.
source to share
Joins need to be done this way because they come from different tables. You cannot use CASE to switch the table from which a query exits: a statement must be parsed, including its data sources, before there are values to compare.
Moreover, CASE returns values, whereas table names ... I don't know the technical term .. structural query components. For the same reason, you cannot select from the "Cool_Stuff" table:
select * from "Cool_" + "Stuff"
Hope this answers your question!
source to share
You can do the following
select master.* , COALESCE(b.descriptor, c.descriptor, d.descriptor, e.descriptor, f.descriptor) as descriptor
from
( SELECT a.folderid, a.foldername, a.contenttype
FROM t_folders a
WHERE a.folderid = $folderId
ORDER BY a.folderid DESC ) master
LEFT JOIN t_files b
ON master.contenttype = 'file' AND master.contentid = b.fileid
LEFT JOIN t_links c
ON master.contenttype = 'link' AND master.contentid = c.linkid
LEFT JOIN t_extfiles d
ON master.contenttype = 'extfile' AND master.contentid = d.extfileid
LEFT JOIN t_videos e
ON master.contenttype = 'video' AND master.contentid = e.videoid
LEFT JOIN t_exams f
ON master.contenttype = 'exam' AND master.contentid = f.examid
When minimizing the result in table a, the join operation can be optmizsed
source to share