Re: Index not being used

On Thu, Jun 17, 2004 at 06:38:08AM +0200, Manfred Koizar wrote:
> This index is useless, drop it. Is there an index on books(id)?

Yes it's a primary key.

> Try
> EXPLAIN ANALYSE
> SELECT *
> FROM orders_and_books AS o
> INNER JOIN books AS b ON o.book_id = b.id
> WHERE o.order_id = 753;
>
> This should give a nested loop using primary key index scans on both
> tables. Then add
>
> LEFT JOIN publishers ON publisher_id=publishers.id
> LEFT JOIN places ON place_id=places.id
> ...
>
> one by one until the plan changes to hash joins again and show us the
> results.

The inner join really speeds up the script. From it's
current 11 second runtime to 0.3 seconds. I'm curious for
future optimization efforts as to why this is the case.
That is why an inner join as is shown in your query yeilds
such a more efficient query than a regular join with a
where clause. Looks like I have some reading to do.

Thanks for your help with this. Very dramatic improvement.

S

--
Shane Wegner
http://www.cm.nu/~shane/