Hello firebird-support,

Here is my source query:

------------------------------------------------
select
"book_id"
from
"bookgenres",
"genre_children"
WHERE
"bookgenres"."genre_id" = "genre_children"."genre_child_id" AND
"genre_children"."genre_id" = 1345
------------------------------------------------

The following used the indexes on [B]genre_id[/B] for both tables and
returns me 16 book_id in a few miliseconds.

However, I need the actual books with these IDs so I do:
------------------------------------------------
SELECT * from "books" where "books"."book_id" IN
(select
"book_id"
from
"bookgenres",
"genre_children"
WHERE
"bookgenres"."genre_id" = "genre_children"."genre_child_id" AND
"genre_children"."genre_id" = 1345)
------------------------------------------------

So I expected it to use the [b]books.book_id[/b] index against each of
the 16 results. Strangely Firebird insists on doing here a fulltable
scan on [b]books[/b].

It is even weirder since if I do it like that:

------------------------------------------------
SELECT * from "books" where "books"."book_id" IN
(66,36,87,27,76......)
------------------------------------------------

Thus manually write these 16 ids in, it does it quickly it uses the
index as it should.

Below are the PLANs for all these queries:\

First query - Fetches the 16 book ids:
------------------------------------------------
Query Time
------------------------------------------------
Prepare : 0
Execute : 16

Plan:
------------------------------------------------
PLAN JOIN (genre_children INDEX (IDX_genre_children),bookgenres INDEX
(IDX_bookgenres1))

Enchanced Info:
+--------------------------+-------+-----------+
| Table Name | Index | Non-Index |
| | reads | reads |
+--------------------------+-------+-----------+
| bookgenres| 16 | 0 |
| genre_children| 1 | 0 |
+--------------------------+-------+-----------+

Second query - Fetches the book themselves using the first query as a
subquery:

------------------------------------------------
Query Time
------------------------------------------------
Prepare : 0
Execute : 1,703

Plan:
------------------------------------------------
PLAN JOIN (bookgenres INDEX (IDX_bookgenres),genre_children INDEX
(IDX_genre_children1,IDX_genre_children))
PLAN (books NATURAL)

Enchanced Info:
+--------------------------+-------+-----------+
| Table Name | Index | Non-Index |
| | reads | reads |
+--------------------------+-------+-----------+
| RDB$FIELDS| 4 | 0 |
| RDB$INDEX_SEGMENTS| 1 | 0 |
| RDB$RELATION_FIELDS| 4 | 0 |
| RDB$RELATIONS| 2 | 0 |
| RDB$RELATION_CONSTRAINTS| 1 | 0 |
| bookgenres| 45,04 | 0 |
| genre_children| 16 | 0 |
| books| 0 | 18,054 |
+--------------------------+-------+-----------+

Finally, this is the query that fetches from [b]books[/b] by writing
the ids numbers manually:

------------------------------------------------
Query Time
------------------------------------------------
Prepare : 31
Execute : 32

Plan:
------------------------------------------------
PLAN (books INDEX
(PK_books,PK_books,PK_books,PK_books,PK_books,PK_books,PK_books,PK_books,PK_books,PK_books,PK_books,PK_books,PK_books,PK_books,PK_books,PK_books))

Enchanced Info:
+--------------------------+-------+-----------+
| Table Name | Index | Non-Index |
| | reads | reads |
+--------------------------+-------+-----------+
| RDB$FIELDS| 4 | 0 |
| RDB$INDEX_SEGMENTS| 1 | 0 |
| RDB$RELATION_FIELDS| 4 | 0 |
| RDB$RELATIONS| 2 | 0 |
| RDB$RELATION_CONSTRAINTS| 1 | 0 |
| books| 16 | 0 |
+--------------------------+-------+-----------+

Regards,
Vitaly Belman

ICQ: 1912453
AIM: VitalyB1984
MSN: tmdagent@...
Yahoo!: VitalyBe