Reputation: 4323
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.
Upvotes: 17
Views: 34174
Reputation: 19012
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.
Upvotes: 3
Reputation: 453990
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)
Upvotes: 13
Reputation: 370
Looks like you are wanting the r.id values that are not in the tblc_comment_manager table.
Use a Not In
select r
.id
as id
from tbl_rls
as r
where r
.id
not in (select distinct cm
.rlsc_id
from tblc_comment_manager
as cm
)
Upvotes: 1
Reputation: 12870
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.
Upvotes: 27
Reputation: 14120
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
Upvotes: 4