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)

Community · Accepted Answer · 2017-05-23 11:47:42Z

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, 2017 at 11:47

CommunityBot

11 silver badge

answered Apr 12, 2011 at 4:51

gbn

435k85 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thomas Over a year ago

Learn something new everyday. Never heard the voodoo of Count(1). I wonder if that was created by some SEO guy...

Mike Over a year ago

Thanks! Really Box 9 had the answer below too but your explanation of the "superstition" and the additional info helped.

David Tang · Accepted Answer · 2011-04-12 04:50:41Z

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, 2011 at 4:50

David Tang

93.8k32 gold badges169 silver badges149 bronze badges

Comments

Coxy · Accepted Answer · 2011-04-12 04:55:02Z

0

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.

answered Apr 12, 2011 at 4:55

Coxy

8,9454 gold badges42 silver badges63 bronze badges

Collectives™ on Stack Overflow

SQL Select Count in Where Clause Performance issue

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related