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.
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.
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)
tbl_rls.id those are not in tblc_comment_managerWhat 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
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.
NOT EXISTS are more efficient (though care must be taken about NULLs for the first one). For MySQL I'm not sure what the recommendation is.
WHERE cm.rlsc_id IS NULLLooks 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)
idcolumns should have indexes