EXPLAIN EXTENDED

How to create fast database queries

Archive for April 18th, 2009

Counting missing rows

Comments enabled. I *really* need your comment

From Stack Overflow:

In my first programming job I was told that a query within the IN() predicate gets executed for every row contained in the parent query, and therefore using IN should be avoided.

For example, given the query:

SELECT  COUNT(*)
FROM    Table1
WHERE   Table1Id NOT IN
	(
	SELECT	Table1Id
	FROM	Table2
	WHERE	id_user = 1
	)
Rows Inner subquery executions
10 10
100 100
1000 1000
10000 10000

Is this correct? How does the IN predicate actually work?

This is the 1st of 5 articles covering implementation of NOT IN predicate in several RDBMS'es:

In this article I will describe how MySQL implements this predicate.

Let's create the sample tables:
Read the rest of this entry »

Written by Quassnoi

April 18th, 2009 at 11:00 pm

Posted in MySQL