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)))

Structure:
RECREATE TABLE CATEGORYLIST
(
ID INTEGER NOT NULL,
SONGID INTEGER DEFAULT 0 NOT NULL,
CATEGORYID INTEGER DEFAULT 0 NOT NULL,
SORTID FLOAT DEFAULT 0 NOT NULL
);

RECREATE TABLE SONGLIST
(
ID INTEGER NOT NULL,
FILENAME VARCHAR( 250) DEFAULT '' NOT NULL COLLATE NONE,
DISKID INTEGER DEFAULT 0 NOT NULL,
FLAGS VARCHAR( 10) DEFAULT 'NNNNNNNNNN' NOT NULL COLLATE NONE,
SONGTYPE CHAR( 1) DEFAULT 'S' NOT NULL COLLATE NONE,
STATUS SMALLINT DEFAULT 0 NOT NULL,
WEIGHT FLOAT DEFAULT 50 NOT NULL,
BALANCE FLOAT DEFAULT 0 NOT NULL,
DATE_ADDED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
DATE_PLAYED TIMESTAMP DEFAULT NULL,
DATE_ARTIST_PLAYED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
DURATION INTEGER DEFAULT 0 NOT NULL,
TITLE VARCHAR( 255) DEFAULT '' NOT NULL COLLATE NONE,
ALBUM VARCHAR( 255) DEFAULT '' NOT NULL COLLATE NONE,
LABEL VARCHAR( 255) DEFAULT '' NOT NULL COLLATE NONE,
PLINE VARCHAR( 50) DEFAULT '' NOT NULL COLLATE NONE,
TRACKNO SMALLINT DEFAULT 0 NOT NULL,
COMPOSER VARCHAR( 100) DEFAULT '' NOT NULL COLLATE NONE,
ISRC VARCHAR( 50) DEFAULT '' NOT NULL COLLATE NONE,
CATALOG VARCHAR( 50) DEFAULT '' NOT NULL COLLATE NONE,
UPC VARCHAR( 50) DEFAULT '' NOT NULL COLLATE NONE,
FEEAGENCY VARCHAR( 20) DEFAULT '' NOT NULL COLLATE NONE,
ALBUMYEAR VARCHAR( 4) DEFAULT '0' NOT NULL COLLATE NONE,
GENRE VARCHAR( 20) DEFAULT '' NOT NULL COLLATE NONE,
WEBSITE VARCHAR( 255) DEFAULT '' NOT NULL COLLATE NONE,
BUYCD VARCHAR( 255) DEFAULT '' NOT NULL COLLATE NONE,
INFO BLOB SUB_TYPE 0 SEGMENT SIZE 80,
LYRICS BLOB SUB_TYPE 0 SEGMENT SIZE 80,
PICTURE VARCHAR( 255) DEFAULT '' NOT NULL COLLATE NONE,
COUNT_PLAYED INTEGER DEFAULT 0 NOT NULL,
COUNT_REQUESTED INTEGER DEFAULT 0 NOT NULL,
LAST_REQUESTED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
COUNT_PERFORMANCES INTEGER DEFAULT 0 NOT NULL,
XFADE VARCHAR( 50) DEFAULT '' NOT NULL COLLATE NONE,
ARTIST VARCHAR( 250) DEFAULT '' NOT NULL
);

CREATE ASC INDEX SONGLIST_ARTIST_A ON SONGLIST (ARTIST);
=============================

Louis


[Non-text portions of this message have been removed]