Louis,

As the others stated, you don't have a foreign key relation (or an
appropriate index) between the tables CATEGORYLIST and SONGLIST.

I do have a FREECDDB as Firebird DB with
340.000+ Discs (table DISCS)
and more than 4.2 Million Tracks (Table TRACKS).

Tables DDL looks like:

CREATE TABLE DISCS (
DISCID D_DISCID NOT NULL,
ARTIST D_ARTIST,
DTITLE D_DISCTITLE COLLATE PXW_INTL
);

CREATE INDEX DISCS_IDX1 ON DISCS (DISCID);

CREATE TABLE TRACKS (
DISCID D_DISCID NOT NULL,
TRACKID D_TRACKID,
TTITLE D_TRACKTITLE COLLATE PXW_INTL
);

CREATE INDEX TRACKS_IDX1 ON TRACKS (DISCID);
CREATE INDEX TRACKS_IDX2 ON TRACKS (TRACKID);


A query like:
SELECT FIRST 100 * FROM
DISCS D
JOIN TRACKS T ON D.DISCID = T.DISCID
WHERE D.ARTIST CONTAINING 'ZAPPA'
needs almost nothing to execute, even though I'm using containing.

Execute Time : 150,00 ms
Avg fetch time: 3,85 ms


So, if you're going to index CATEGORY.SONGID and SONGLIST.ID you should
have a pretty fast query, too.

At the moment you're joining WITHOUT any index 5000 records from
SONGLIST with 8000 records in CATEGORYLIST, which means that you several
millions reads.

Luc.