--- In firebird-support@yahoogroups.com, Vitaly Belman <vitalib@0...>
wrote:
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)
> ------------------------------------------------

If so, why you ask FB to scan books and for each book to check if it
belongs to result of subquery? Why not something like

SELECT "books".*
From "bookgenres",
"genre_children",
"books"
WHERE
"bookgenres"."genre_id" = "genre_children"."genre_child_id"
AND "genre_children"."genre_id" = 1345
And "books"."book_id"="???"."book_id"

Note 1 - ??? appeared here because you don't specify which of tables
"bookgenres" and "genre_children" have this column. Note FB could be
confused too and you can get wrong result.

Note 2 - (friendly tip, not an attack) most of info you provide
further is of little interest and even bother to understand you.
Usually queries and plans are enough, if we'll encounter something
really strange, you'll be asked for needed details.

Note 3 - search in options of your tool for something like 'Always
capitalize database objects names' to get rid of this double quotes.

Note 4 - if I'm right in assumption you speak Russian, you can get
support at news://forums.demo.ru/epsylon.public.interbase

Best regards,
Alexander