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)",
--
Wolfgang
