17

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.

John Fowler
  • 3,173
  • 3
  • 18
  • 31
Arshdeep
  • 4,281
  • 7
  • 31
  • 46

6 Answers6

27

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.

bpeterson76
  • 12,918
  • 5
  • 49
  • 82
  • 1
    Holy hot damn was this the culprit. I went from 10 minutes to 0.9 ms. Any recommendation on a MySQL analysis tool that can tell you which indexes you should have on your database? – Joshua Pinter Aug 15 '17 at 23:33
  • Indexing did the trick. query now runs in 233ms was previously 48s – Kxng Kombian Dec 07 '20 at 14:25
13

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)
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
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
Naktibalda
  • 13,705
  • 5
  • 35
  • 51
4

MySQL's EXPLAIN might help you finding out what is going on.

middus
  • 9,103
  • 1
  • 31
  • 33
3

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.

JohnB
  • 18,046
  • 16
  • 98
  • 110
  • 1
    @JohnB For SQL Server 'NOT IN' and `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. – Martin Smith Jul 15 '10 at 16:31
  • Additionally, others have pointed out the approach: `WHERE cm.rlsc_id IS NULL` – JohnB Jul 15 '10 at 16:33
  • @Martin: someone who read **High Performance MySQL** might know the answer - http://www.amazon.com/High-Performance-MySQL-Jeremy-Zawodny/dp/0596003064 – JohnB Jul 15 '10 at 16:34
  • Neverthess, he has a few options that would take him 5 mins to test. Arsheep, please post your results! – JohnB Jul 15 '10 at 16:35
  • @Martin: when you make a column a PK in MySQL, does it automatically create an index for that column? If not, create indexes for your PK columns Arsheep! – JohnB Jul 15 '10 at 16:36
  • @JohnB - Yep quick test this end seems to say that you'll automagically get an index on the PK but I guess that might not help `tblc_comment_manager` – Martin Smith Jul 15 '10 at 16:42
  • @Martin Smith: Depends on if the columns are [nullable](http://explainextended.com/2010/05/27/left-join-is-null-vs-not-in-vs-not-exists-nullable-columns/) or [not](http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/) – OMG Ponies Jul 15 '10 at 16:51
1

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)

DaveWilliamson
  • 370
  • 1
  • 11