Re: Everlasting SQL query

Hi Joost.

Joost Kraaijeveld wrote:
> I have a customer table (17518 records) and an orders table (88393
> records). One of the columns of orders is customerid, containing the
> customerid (what else, but it is not a foreign key as this table is
> imported from a database that did not support foreign keys).
>
> If I do this query (with pgadmin III):
>
> select customer.id, customer.name, orders.id from customers, orders
> order by customer.id, orders.id limit 25
>
> The query runs forever (the longest I let it run is 500 seconds).

No wonder. You are retrieving 1548468574 rows. You are trying to perform
a JOIN, but without specifying which fields to join on. So the query
works with cartesian product of these two table (all possible
combinantions), which is 17518 * 88393 = 1548468574 rows.

You want:

select customer.id, customer.name, orders.id
from customers JOIN orders ON customers.id=orders.customerid
order by customer.id, orders.id
limit 25

or alternatively:

select customer.id, customer.name, orders.id
from customers, orders
where customers.id=orders.customerid
order by customer.id, orders.id
limit 25

I prefer the first notation, though.

--
Michal Taborsky
http://www.taborsky.cz