Is there any other way to fire this query

Question

SELECT CategoryID, CategoryName
FROM testcategory
WHERE CategoryID NOT 
IN (
  SELECT ChildMuzeCategoryID
  FROM testsubcategory
)

First table contain 75k Second table contain 45k records

possible duplicate of [SQL: "NOT IN" alternative for selecting rows based on values of *different* rows?](http://stackoverflow.com/questions/1286155/sql-not-in-alternative-for-selecting-rows-based-on-values-of-different-rows) — Eric J., Jun 27 '12 at 04:46
safety tip: index your column which appears in `where/ON` condition — xkeshav, Jun 27 '12 at 04:48
Sidenote: Read about mysqli and/or PDO. MySQL is going to be obsolate soon. — , Jun 27 '12 at 04:48

score 4 · Accepted Answer · edited Jun 28 '12 at 14:47

4

Try to use join:

SELECT CategoryID, CategoryName
FROM testcategory
LEFT JOIN testsubcategory ON testcategory.CategoryID = testsubcategory.ChildMuzeCategoryID
WHERE testsubcategory.ChildMuzeCategoryID IS NULL

edited Jun 28 '12 at 14:47

Intekhab Khan

1,775
4
18
28

answered Jun 27 '12 at 04:47

xdazz

158,678
38
247
274

Precisely! Query with JOIN executes a lot faster than query with IN. – Jonas T Jun 27 '12 at 04:49
it would be more faster if we write `SELECT STRAIGHT_JOIN ` [Reference](http://stackoverflow.com/questions/5818837/why-does-straight-join-so-drastically-improve-this-query-and-what-does-it-mean) – xkeshav Jun 27 '12 at 04:53
Thanks it is fast to previous one but still slow overall.. Is there is any way top process this script via some other thing...I have heard about Hive SQL.. – Intekhab Khan Jun 27 '12 at 05:28

score 0 · Answer 2 · answered Jun 27 '12 at 04:46

0

This query is perfectly fine, there's no way to rewrite it that would actually cause it to perform any better.

answered Jun 27 '12 at 04:46

Sean Johnson

5,567
2
17
22

score 0 · Answer 3 · answered Jun 27 '12 at 04:46

What you have written is the best way to do and there is no better way, but I have a suggestion:

Since when working with large amount of rows, it would be better if you have some flag like testsubcategory and update it to 1 (boolean) while updating the testsubcategory table. And we can easily use this query:

SELECT `CategoryID`, `CategoryName` FROM `testcategory` WHERE NOT `testsubcategory` = 1;

Is there any other way to fire this query

3 Answers3