Use "FROM tracks, album" in place of "FROM album, tracks".
On Sat, 2005-05-07 at 17:06 +0200, Wolfgang Rohdewald wrote: > Hi, > > I am trying to port some software (muggle media browser, > see http://www.htpc-tech.de/htpc/muggle.htm) to sqlite. > > This query returns at once with mysql: > mysql> SELECT album.title,COUNT(*) AS mgcount FROM album,tracks WHERE > tracks.folder1='ja' AND tracks.folder2='NeilDiamond' AND tracks.folder3='' > AND tracks.folder4='' AND tracks.sourceid=album.cddbid GROUP BY album.title > ORDER BY mgcount desc,album.title; > +----------------------------+---------+ > | title | mgcount | > +----------------------------+---------+ > | Unassigned | 52 | > | Classics - The Early Years | 6 | > | His Very Best | 3 | > +----------------------------+---------+ > 3 rows in set (0.00 sec) > > but it takes about 10 seconds with sqlite. > > This one still takes about 10 seconds: > sqlite> SELECT album.title FROM album,tracks WHERE tracks.folder1='ja' AND > tracks.folder2='NeilDiamond' ANDtracks.folder3='' AND tracks.folder4='' AND > tracks.sourceid=album.cddbid; > But if I remove the last 2 WHERE clauses, it takes about 1 second - still > rather much: > sqlite> SELECT album.title FROM album,tracks WHERE tracks.folder1='ja' AND > tracks.folder2='NeilDiamond' ANDtracks.sourceid=album.cddbid GROUP BY > album.title; > > How can I speed this up? How can I check how sqlite optimizes this query? > I am using the debian unstable packages. > > sqlite> select count(*) from tracks; > 2631 > sqlite> select count(*) from album; > 657 > > the schema: > "CREATE TABLE album ( " > "artist varchar(255) default NULL, " > "title varchar(255) default NULL, " > "cddbid varchar(20) NOT NULL default '', " > "coverimg varchar(255) default NULL, " > "covertxt mediumtext, " > "modified date default NULL, " > "genre varchar(10) default NULL, " > "PRIMARY KEY (cddbid))", > > "CREATE TABLE tracks ( " > "artist varchar(255) default NULL, " > "title varchar(255) default NULL, " > "genre1 varchar(10) default NULL, " > "genre2 varchar(10) default NULL, " > "year smallint(5) default NULL, " > "lang varchar(4) default NULL, " > "type tinyint(3) default NULL, " > "rating tinyint(3) default NULL, " > "length smallint(5) default NULL, " > "source tinyint(3) default NULL, " > "sourceid varchar(20) default NULL, " > "tracknb tinyint(3) default NULL, " > "mp3file varchar(255) default NULL, " > "condition tinyint(3) default NULL, " > "voladjust smallint(6) default '0', " > "lengthfrm mediumint(9) default '0', " > "startfrm mediumint(9) default '0', " > "bpm smallint(6) default '0', " > "lyrics mediumtext, " > "bitrate varchar(10) default NULL, " > "created date default NULL, " > "modified date default NULL, " > "backup tinyint(3) default NULL, " > "samplerate int(7) default NULL, " > "channels tinyint(3) default NULL, " > "id integer PRIMARY KEY autoincrement, " > "folder1 varchar(255), " > "folder2 varchar(255), " > "folder3 varchar(255), " > "folder4 varchar(255)) ", > "CREATE INDEX tracks_title on tracks (title)", > "CREATE INDEX tracks_mp3file on tracks (mp3file)", > "CREATE INDEX tracks_genre1 on tracks (genre1)", > "CREATE INDEX tracks_genre2 on tracks (genre2)", > "CREATE INDEX tracks_year on tracks (year)", > "CREATE INDEX tracks_lang on tracks (lang)", > "CREATE INDEX tracks_artist on tracks (artist)", > "CREATE INDEX tracks_folder1 on tracks (folder1)", > "CREATE INDEX tracks_folder2 on tracks (folder2)", > "CREATE INDEX tracks_folder3 on tracks (folder3)", > "CREATE INDEX tracks_folder4 on tracks (folder4)", >
