check your indexes you have only one created for songlist isn't ID a
primary key-field ?
--->

(songlist.ID = categorylist.songID)

<---- the main JOIN condition has no index as I read ...

---> PLAN (SONGLIST NATURAL...)


some more indexes will change the picture dramatically I guess
yours
Claus

Louis Louw 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);
>=============================
>
>Louis
>
>
>[Non-text portions of this message have been removed]
>
>
>
>To unsubscribe from this group, send an email to:
>firebird-support-unsubscribe@yahoogroups.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>
>.
>