31.5 seconds? Thirty years ago, people would be thrilled by that kind of
performance - you've got no reason to complain!

No, seriously, you probably have an index for category.categoryID which
isn't selective at all. Remove this index and create primary keys for
songlist.ID and categorylist.ID (if you haven't done so already) and your
execution time will decrease considerably. Depending on your data and
queries, you may or may not want to create indexes for date_played and
date_artist_played. Finally, I find it strange to order by something not
included in the select, but it is just a thought that never have struck me,
so it may or may not be legal.

Set

At 08:56 26.06.2003 +0200, you wrote:

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