0

I have a table like this :

DESC mytable

id  | parent
1   | 2
2   | null
3   | null
4   | null
5   | 3
... | ...

3 kind of entries :

  • Entry which is not a parent and has no parent (ex: 4)
  • Entry which is not a parent and has a parent (ex: 1,5)
  • Entry which is a parent [and has no parent] (ex: 2,3)

I'm trying to get every row which are not a parent.

At first I tried this :

SELECT * FROM mytable WHERE id NOT IN (SELECT DISTINCT parent FROM mytable);

It was too slow, so I tried this :

SELECT m.* FROM mytable m
# tmp.* will contain the first child
LEFT JOIN mytable tmp ON tmp.parent = m.id
# only rows with no children
WHERE tmp.id IS NULL

But it is still too slow...

On my second query, I know that MySQL fetch every children but only store the first one in tmp.*, but I can't find an efficient way to limit the search to 1 child. (I looked here and on other websites)

Do you have any idea on how I could improve exec time ?

Thanks for any help

Community
  • 1
  • 1
Apolo
  • 3,844
  • 1
  • 21
  • 51

1 Answers1

2

A join would work of course, but you could also use exists... I'm not sure which one will run better in an explain. It might help if you index the parent column as well.

Try this:

SELECT m.* FROM mytable m WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE parent = m.ID)
Brian Driscoll
  • 19,373
  • 3
  • 46
  • 65