Mysql query with Left Join is too very slow
Solution 1
Consider also indexing your tables. We're running multiple left joins on a 1million+ record table that doesn't take more than a second or two to return results.
Solution 2
Do you really need the !=
or is it meant to be =
?
select `r`.`id` as `id` from `tbl_rls` as `r`
left join `tblc_comment_manager` as `cm`
on `cm`.`rlsc_id`!=`r`.`id
This will select nearly the cartesian product of the 2 tables. (I guess around 60 million rows)
Edit: From the comment
yes it is " != " to match tbl_rls.id those are not in tblc_comment_manager
I think this is what you need if you want to use the outer join
approach.
select DISTINCT `r`.`id` as `id` from `tbl_rls` as `r`
left join `tblc_comment_manager` as `cm`
on `cm`.`rlsc_id`=`r`.`id
WHERE `cm`.`rlsc_id` IS NULL
Although my preference is usually
select `r`.`id` as `id`
from `tbl_rls`
as `r`
WHERE NOT EXISTS(
SELECT * FROM `tblc_comment_manager` as `cm`
WHERE `cm`.`rlsc_id`=`r`.`id)
Solution 3
MySQL's EXPLAIN might help you finding out what is going on.
Solution 4
What do you want to select?
Use this query if you want to find tbl_rls records that haven't matching records in other table
select `r`.`id`
from `tbl_rls` as `r`
left join `tblc_comment_manager` as `cm`
on `cm`.`rlsc_id`=`r`.`id
where `cm`.`rlsc_id` IS NULL
Solution 5
You may need to provide more info. But one thing I would try is reversing the order of your ON clause (because it's so easy):
ON r.id != cm.rlsc_id
Edit: and you should put indexes on your PK (id) columns.
But I think this article might help you out.
Basically it says that NOT IN
takes less resources than LEFT JOIN
. A commenter in that article mentions using NOT EXISTS
is best.
Also, I'm not sure this is accurate or not, but this article says that NOT IN
does a full table scan, and NOT EXISTS
can use an index.
Arshdeep
Updated on November 04, 2020Comments
-
Arshdeep over 3 years
Query:
select `r`.`id` as `id` from `tbl_rls` as `r` left join `tblc_comment_manager` as `cm` on `cm`.`rlsc_id` != `r`.`id`
Both tables have 8k records but why is it very slow, taking 2-3 minutes and more sometimes?
OMG , this query makes mysql server down. Will get back to you peoples in a second :(
All peoples those suggested Indexing the columns are all Correct. Yeh the query i wrote was silly and buggy. Thanks correcting me.