SQL Select Count in Where Clause Performance issue

Question

I have the following SQL query that performs horribly due to the select count(1) statement in the where clause. Can anyone suggest a way that would speed this up? The idea is that I only want rows returned where there is one invoice found.

SELECT people.name, people.address 
FROM people 
WHERE ((SELECT COUNT(1) FROM invoices WHERE invoices.pid = people.id)=1)

score 4 · Accepted Answer · edited May 23 '17 at 11:47

4

COUNT(1) is superstition
What you have is a count per row of people = a cursor/loop like action

So, try a JOIN like this

SELECT people.name, people.address 
FROM
   people 
   JOIN
   invoices ON invoices.pid = people.id
GROUP BY
   people.name, people.address 
HAVING
   COUNT(*) = 1

I'd also hope you have indexes, at least on invoices.pid and people.pid, name, address

edited May 23 '17 at 11:47

Community

1
1

answered Apr 12 '11 at 04:51

gbn

422,506
82
585
676

Learn something new everyday. Never heard the voodoo of Count(1). I wonder if that was created by some SEO guy... – Thomas Apr 12 '11 at 05:08
Thanks! Really Box 9 had the answer below too but your explanation of the "superstition" and the additional info helped. – Mike Apr 14 '11 at 04:11

score 1 · Answer 2 · answered Apr 12 '11 at 04:50

1

Use a JOIN:

SELECT people.name, people.address
FROM people
JOIN invoices ON invoices.pid = people.id
GROUP BY people.name, people.address
HAVING Count(*) = 1

answered Apr 12 '11 at 04:50

David Tang

92,262
30
167
149

score 0 · Answer 3 · answered Apr 12 '11 at 04:55

Joining the tables is probably going to be much better in practice and in performance, I should think.

SELECT people.name, people.address 
FROM people INNER JOIN invoices ON invoices.pid = people.id

Edit due to OP being edited: do you want only those people who have exactly one invoice? If so then disregard this and look at one of the other answers.

SQL Select Count in Where Clause Performance issue

3 Answers3