Hi,

> Having a problem with a slow query in FireBird.
>
> MySQL takes only 0.1seconds, but FireBird takes a whopping 31.5 seconds

to execute this query on a 2.4 GHz machine!

> Even if I specify a PLAN things don't improve much. (It seems to even

completely ignore my custom plans sometimes, reporting a completely
different plan in the end)

>
> The songlist table contains about 5000 entries, and the categorylist table

around 8000 records (But only about 3000 records matching the join, and only
around 400 matching the result)

>
> Any clues on why this is so slow?
>
> =============================
> SELECT
> FIRST 100 categorylist.ID as listID, songlist.ID as songID,

songlist.artist

> FROM
> songlist join categorylist on (songlist.ID = categorylist.songID)
> WHERE
> (categorylist.categoryID = 8)
> AND (date_played < '06/25/2003 10:12:24.081')
> AND (date_artist_played < '06/25/2003 12:12:24.081')
> AND (status = 0)
> AND (artist <> '')
> AND (artist <> 'Elton John')
> ORDER BY balance ASC, weight DESC
>
> PLAN:
>
> PLAN SORT (JOIN (SONGLIST NATURAL,CATEGORYLIST INDEX

(IND_CATEGORYLIST_CATEGORYID)))

What's the DDL for this index : IND_CATEGORYLIST_CATEGORYID ?

Regards,
Arno