SQL Select Count in Where Clause Performance issue

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)

---

• 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

---

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

---

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.