Andy
I get this explain output:
order|from|detail
0|0|TABLE mediaitem AS a WITH INDEX mediaitem_type
1|1|TABLE mediaitem AS b WITH INDEX mediaitem_type
2|2|TABLE container AS w USING PRIMARY KEY
3|3|TABLE container AS x USING PRIMARY KEY
4|4|TABLE container AS y USING PRIMARY KEY
5|5|TABLE container AS z USING PRIMARY KEY
Media Item Type is a terrible index! It only has 3 values so drop it.
After dropping the index i get this explain:
drop index mediaitem_type
...> ;
sqlite> .read media.sql
order|from|detail
0|0|TABLE mediaitem AS a WITH INDEX mediaitem_title ORDER BY
1|1|TABLE mediaitem AS b WITH INDEX mediaitem_containerID
2|2|TABLE container AS w USING PRIMARY KEY
3|3|TABLE container AS x USING PRIMARY KEY
4|4|TABLE container AS y USING PRIMARY KEY
5|5|TABLE container AS z USING PRIMARY KEY
See if that is better?
Andy Smith <[EMAIL PROTECTED]> wrote: I have to get this down to < 1 sec. I
have 50 queries that are all
formatted similar. We have created another schema where the container
is split into smaller tables which might help in speeding this up. From
what I see the Left joins are killing the speed.
Here is what I am seeing when running explain query plan:
sqlite> explain query plan SELECT a.id, a.title, a.type, a.dateAdded,
a.url, a.path, a.containerID, a.mimeType, a.width, a.height, a.genreID,
a.thumbnailID, a.releaseYearID, a.artistID, w.title AS containerName,
x.title AS genreName, y.title AS releaseYearName, z.title AS artistName
FROM mediaitem AS a LEFT JOIN mediaitem AS b ON
b.containerID=a.containerID LEFT JOIN container AS w ON
w.id=a.containerID LEFT JOIN container AS x ON x.id=a.genreID LEFT JOIN
container AS y ON y.id=a.releaseYearID LEFT JOIN container AS z ON
z.id=a.artistID WHERE a.type=1 AND b.type=0 AND b.title LIKE 'Opus%'
ESCAPE '\' ORDER BY a.title, a.id LIMIT 0,9;
0|0|TABLE mediaitem AS a WITH INDEX mediaitem_title ORDER BY
1|1|TABLE mediaitem AS b WITH INDEX mediaitem_containerID
2|2|TABLE container AS w USING PRIMARY KEY
3|3|TABLE container AS x USING PRIMARY KEY
4|4|TABLE container AS y USING PRIMARY KEY
5|5|TABLE container AS z USING PRIMARY KEY
Schema:
sqlite> .schema
CREATE TABLE "container" (
-- fundamental container information
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"title" VARCHAR(255) NOT NULL,
-- enumerations
-- 10: music album
-- 11: music artist
-- 12: music genre
-- 13: music composer
-- 14: music conductor
-- 15: release year
-- 16: photo roll
-- 17: photo album / slide show
-- 18: events
-- 19: keyword
-- 20: people
-- 21: places
-- 22: user-defined tag
-- 23: playlist
-- 24: video season
-- 25: video series
-- 26: channel
-- 27: director
-- 28: actor
-- 29: producer
-- 30: audio series
-- 31: media folder
"type" INTEGER NOT NULL,
"dateAdded" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
"dateCreated" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
-- generic information about the media container
"details" TEXT DEFAULT NULL,-- lyrics, plot, etc.
"detailsURI" TEXT DEFAULT NULL,-- lyrics, plot, etc.
-- non-NULL only for slideshows and playlists
"duration" INTEGER UNSIGNED
DEFAULT NULL,-- in seconds
"transition" tinyint DEFAULT '0', -- 0: no effect
-- 1: fade in/out
-- non-NULL only for media folders
"upnpShared" tinyint DEFAULT '0',
"parentalLocked" tinyint DEFAULT '0',
-- voting and ratings
"rating" BIGINT UNSIGNED
DEFAULT NULL,
"voteCount" INTEGER UNSIGNED
DEFAULT NULL,
"avgRating" FLOAT DEFAULT NULL,
-- housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
"locked" tinyint DEFAULT '0',
"extended" tinyint DEFAULT '0'
);
CREATE TABLE "containeraux" (
-- used for certain types of containers to ensure title/type uniqueness
"title" VARCHAR(255) NOT NULL REFERENCES "container(title)",
"type" INTEGER NOT NULL REFERENCES "container(type)"
);
CREATE TABLE "extend" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"title" VARCHAR(255) NOT NULL,
"document" TEXT NOT NULL,
"contact" VARCHAR(255) NOT NULL,
"dateAdded" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
-- housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL
);
CREATE TABLE "extension" (
-- fundamental extension information
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"extendID" INTEGER NOT NULL REFERENCES "extend(id)",
-- exactly one of the following is non-NULL
"mediaitemID" INTEGER DEFAULT NULL REFERENCES
"mediaitem(id)",
"containerID" INTEGER DEFAULT NULL REFERENCES
"container(id)",
"keyvalue" VARCHAR(255) NOT NULL,
"valvalue" VARCHAR(255) NOT NULL,
"dateAdded" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
-- housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL
);
CREATE TABLE "mediaitem" (
-- fundamental mediaitem information
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"title" VARCHAR(255) NOT NULL,
-- enumerations
-- 0: audio
-- 1: image
-- 2: video
"type" INTEGER NOT NULL,
"dateAdded" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
-- remote access
"url" TEXT NOT NULL,
-- information about source
"origin" TEXT NOT NULL,
"path" TEXT NOT NULL,
"removable" tinyint DEFAULT '0',
-- identity of the containing object
"containerID" INTEGER NOT NULL REFERENCES
"container(id)",
"containerPOS" UNSIGNED INTEGER
DEFAULT NULL,
"containerDisc" UNSIGNED INTEGER
DEFAULT NULL,
-- generic information about the media item
"mimeType" VARCHAR(255) NOT NULL, -- e.g., image/jpeg
"size" BIGINT UNSIGNED
NOT NULL,
"details" TEXT DEFAULT NULL,-- lyrics, plot, etc.
"detailsURI" TEXT DEFAULT NULL,-- lyrics, plot, etc.
"genreID" INTEGER DEFAULT NULL REFERENCES
"container(id)",
"thumbnailID" INTEGER DEFAULT NULL REFERENCES
"mediaitem(id)",
"thumb320ID" INTEGER DEFAULT NULL REFERENCES
"mediaitem(id)",
"releaseYearID" INTEGER DEFAULT NULL REFERENCES
"container(id)",
-- non-NULL only for audio/video media items
"duration" INTEGER UNSIGNED
DEFAULT NULL,-- in seconds
-- non-NULL only for image/video media items
"width" INTEGER UNSIGNED
DEFAULT NULL,
"height" INTEGER UNSIGNED
DEFAULT NULL,
"colorDepth" INTEGER UNSIGNED
DEFAULT NULL,
-- non-NULL only for audio media items
"artistID" INTEGER DEFAULT NULL REFERENCES
"container(id)",
"bitRate" INTEGER UNSIGNED
DEFAULT NULL,
"sampleRate" INTEGER UNSIGNED
DEFAULT NULL,
"channels" INTEGER UNSIGNED
DEFAULT NULL,
"vbr" tinyint DEFAULT NULL,
"albumGain" tinyint DEFAULT NULL,
"albumType" VARCHAR(255) DEFAULT NULL,
"band" VARCHAR(255) DEFAULT NULL,
"bitDepth" INTEGER UNSIGNED
DEFAULT NULL,
"beatsPerMinute" INTEGER UNSIGNED
DEFAULT NULL,
"completeAlbum" tinyint DEFAULT NULL,
"compression" VARCHAR(255) DEFAULT NULL,
"intensity" INTEGER UNSIGNED
DEFAULT NULL,
"mixAlbum" tinyint DEFAULT NULL,
"peakLevel" INTEGER UNSIGNED
DEFAULT NULL,
"replayGain" INTEGER UNSIGNED
DEFAULT NULL,
-- non-NULL only for image media items
"aperture" INTEGER UNSIGNED
DEFAULT NULL,
"badPixels" tinyint DEFAULT NULL,
"camera" VARCHAR(255) DEFAULT NULL,
"caption" VARCHAR(255) DEFAULT NULL,
"flash" tinyint DEFAULT NULL,
"focalLength" INTEGER UNSIGNED
DEFAULT NULL,
"isoNumber" VARCHAR(255) DEFAULT NULL,
"rotation" INTEGER UNSIGNED
DEFAULT NULL,
"shutterSpeed" INTEGER UNSIGNED
DEFAULT NULL,
-- non-NULL only for video media items
"mpaaRating" VARCHAR(5) DEFAULT NULL, -- one of:
-- G, PG, PG-13, NC-17,
R, or NR
-- historic: G, GP, M,
or X
"mpaaReasons" VARCHAR(7) DEFAULT NULL, -- any of:
-- V, S, L, and/or D
"program" VARCHAR(255) DEFAULT NULL,
"episode" VARCHAR(255) DEFAULT NULL,
-- voting and ratings
"rating" BIGINT UNSIGNED
DEFAULT NULL,
"voteCount" INTEGER UNSIGNED
DEFAULT NULL,
"avgRating" FLOAT DEFAULT NULL,
-- ownership, rights, etc.
"copyright" TEXT DEFAULT NULL,
-- finally, housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
"locked" tinyint DEFAULT '0',
"extended" tinyint DEFAULT '0'
);
CREATE TABLE "tag" (
-- fundamental tag information
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
-- only one of mediaitemID or containerID is non-NULL
-- containerID may be non-NULL, ONLY:
-- IF the parentID's type is "user-defined tag"
-- AND the containerID's type is not "user-defined tag"
-- OR
-- IF the parentID's type is "media folder"
-- AND the containerID's type is also "media folder"
-- OR
-- IF the parentID's type is "music artist"
-- AND the containerID's type is "music album"
"mediaitemID" INTEGER DEFAULT NULL REFERENCES
"mediaitem(id)",
"containerID" INTEGER DEFAULT NULL REFERENCES
"container(id)",
"parentID" INTEGER NOT NULL REFERENCES "container(id)",
"parentPOS" UNSIGNED INTEGER
DEFAULT NULL,
"dateAdded" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
-- housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL
);
CREATE TABLE "user" (
-- fundamental userinformation
"userid" INTEGER PRIMARY KEY AUTOINCREMENT
-- other user-specific attributes go here...
);
CREATE TABLE "usercontainer" (
-- fundamental usercontainer information
"containerID" INTEGER NOT NULL REFERENCES "container(id)",
"userID" INTEGER NOT NULL REFERENCES "user(userid)",
-- user-specific container information
"notes" TEXT DEFAULT NULL,-- user-supplied
"comments" TEXT DEFAULT NULL,
"dateLastPlayed" TIMESTAMP DEFAULT NULL,
"lastPlayedPosition" BIGINT UNSIGNED
DEFAULT NULL,-- in
hundredths-of-a-second
"playCount" INTEGER UNSIGNED
DEFAULT NULL,
"dateLastSkipped" TIMESTAMP DEFAULT NULL,
"skipCount" INTEGER UNSIGNED
DEFAULT NULL,
"rating" INTEGER DEFAULT NULL, -- range: 1..5
-- bookmark information
"parentPOS" UNSIGNED INTEGER -- identifies position of
media
DEFAULT NULL,-- item having bookmark
"bookmark" BIGINT UNSIGNED
DEFAULT NULL,-- in
hundredths-of-a-second
"useBookmark" tinyint DEFAULT NULL,
-- housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL
);
CREATE TABLE "usermediaitem" (
-- fundamental usermediaitem information
"mediaitemID" INTEGER NOT NULL REFERENCES "mediaitem(id)",
"userID" INTEGER NOT NULL REFERENCES "user(userid)",
-- user-specific media item information
"notes" TEXT DEFAULT NULL,-- user-supplied
"comments" TEXT DEFAULT NULL,
"dateLastPlayed" TIMESTAMP DEFAULT NULL,
"lastPlayedPosition" BIGINT UNSIGNED
DEFAULT NULL,-- in
hundredths-of-a-second
"playCount" INTEGER UNSIGNED
DEFAULT NULL,
"dateLastSkipped" TIMESTAMP DEFAULT NULL,
"skipCount" INTEGER UNSIGNED
DEFAULT NULL,
"rating" INTEGER DEFAULT NULL, -- range: 1..5
-- non-NULL only for audio/video media items
"bookmark" BIGINT UNSIGNED
DEFAULT NULL,-- in
hundredths-of-a-second
"useBookmark" tinyint DEFAULT NULL,
-- housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL
);
CREATE UNIQUE INDEX "auxid" ON containeraux(title,type);
CREATE INDEX container_avgRating ON container(avgRating);
CREATE INDEX container_dateAdded ON container(dateAdded);
CREATE INDEX container_id ON container(id);
CREATE INDEX container_title ON container(title);
CREATE INDEX container_type ON container(type);
CREATE INDEX extend_contact ON extend(contact);
CREATE INDEX extend_title ON extend(title);
CREATE INDEX extension_containerID ON extension(containerID);
CREATE INDEX extension_extendID ON extension(extendID);
CREATE INDEX extension_keyvalue ON extension(keyvalue);
CREATE INDEX extension_mediaitemID ON extension(mediaitemID);
CREATE INDEX mediaitem_artistID ON mediaitem(artistID);
CREATE INDEX mediaitem_avgRating ON mediaitem(avgRating);
CREATE INDEX mediaitem_containerID ON mediaitem(containerID);
CREATE INDEX mediaitem_dateAdded ON mediaitem(dateAdded);
CREATE INDEX mediaitem_genreID ON mediaitem(genreID);
CREATE INDEX mediaitem_mpaaRating ON mediaitem(mpaaRating);
CREATE INDEX mediaitem_releaseYearID ON mediaitem(releaseYearID);
CREATE INDEX mediaitem_thumb320ID ON mediaitem(thumb320ID);
CREATE INDEX mediaitem_thumbnailID ON mediaitem(thumbnailID);
CREATE INDEX mediaitem_title ON mediaitem(title);
CREATE INDEX mediaitem_type ON mediaitem(type);
CREATE INDEX tag_containerID ON tag(containerID);
CREATE INDEX tag_mediaitemID ON tag(mediaitemID);
CREATE INDEX tag_parentID ON tag(parentID);
CREATE UNIQUE INDEX "ucid" ON
usercontainer(containerID,userID);
CREATE UNIQUE INDEX "ulid" ON
usermediaitem(mediaitemID,userID);
CREATE INDEX usercontainer_containerID ON usercontainer(containerID);
CREATE INDEX usercontainer_userid ON usercontainer(userid);
CREATE INDEX usermediaitem_mediaitemID ON usermediaitem(mediaitemID);
CREATE INDEX usermediaitem_userid ON usermediaitem(userid);
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote
Sent: Wednesday, April 09, 2008 11:25 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Left Join help
Andy Smith wrote:
> I have quiet a few queries similar to this doing multiple Left Joins
and
> they run extremely slow > 6 secs. Is there a better way to be writing
> the below query for sqlite.
>
Andy,
You didn't show your table and index definitions, so it's hard to be
sure about what would be best.
The following trace shows what I think your tables should look like, and
a couple of indexes that will help for a slightly rearranged version of
your query. The query plan uses the indexes to locate the subset of the
records with type=0, then checks their title, next it uses the other
index to find the matching records and checks that their type=1. These
records should lead directly to the required container records using the
primary key on that table. I would suspect this is reasonably fast. Let
me know if it helps.
HTH
Dennis Cote
SQLite version 3.5.7
Enter ".help" for instructions
sqlite>
sqlite> create table container (
...> id integer primary key,
...> title text
...> );
sqlite>
sqlite> create table mediaitem (
...> id integer primary key,
...> title text,
...> type integer,
...> containerID integer references container,
...> genreID integer references container,
...> thumbnailID integer references container,
...> releaseYearID integer references container,
...> artistID integer references container,
...> dateAdded,
...> url,
...> path,
...> mimeType,
...> width,
...> height
...> );
sqlite>
sqlite> create index media_type on mediaitem(type);
sqlite> create index media_container on mediaitem(containerID);
sqlite>
sqlite> explain query plan
...> SELECT
...> a.id,
...> a.title,
...> a.type,
...> a.dateAdded,
...> a.url,
...> a.path,
...> a.containerID,
...> a.mimeType,
...> a.width,
...> a.height,
...> a.genreID,
...> a.thumbnailID,
...> a.releaseYearID,
...> a.artistID,
...> w.title AS containerName,
...> x.title AS genreName,
...> y.title AS releaseYearName,
...> z.title AS artistName
...> FROM mediaitem AS b
...> LEFT JOIN mediaitem AS a ON b.containerID=a.containerID
...> LEFT JOIN container AS w ON w.id=a.containerID
...> LEFT JOIN container AS x ON x.id=a.genreID
...> LEFT JOIN container AS y ON y.id=a.releaseYearID
...> LEFT JOIN container AS z ON z.id=a.artistID
...> WHERE b.title LIKE 'Opus%' ESCAPE '\'
...> AND b.type=0
...> AND a.type=1
...> ORDER BY a.title, a.id
...> LIMIT 0,9;
0|0|TABLE mediaitem AS b WITH INDEX media_type
1|1|TABLE mediaitem AS a WITH INDEX media_container
2|2|TABLE container AS w USING PRIMARY KEY
3|3|TABLE container AS x USING PRIMARY KEY
4|4|TABLE container AS y USING PRIMARY KEY
5|5|TABLE container AS z USING PRIMARY KEY
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
This message is confidential to Prodea Systems, Inc unless otherwise indicated
or apparent from its nature. This message is directed to the intended recipient
only, who may be readily determined by the sender of this message and its
contents. If the reader of this message is not the intended recipient, or an
employee or agent responsible for delivering this message to the intended
recipient:(a)any dissemination or copying of this message is strictly
prohibited; and(b)immediately notify the sender by return message and destroy
any copies of this message in any form(electronic, paper or otherwise) that you
have.The delivery of this message and its information is neither intended to be
nor constitutes a disclosure or waiver of any trade secrets, intellectual
property, attorney work product, or attorney-client communications. The
authority of the individual sending this message to legally bind Prodea Systems
is neither apparent nor implied,and must be independently verified.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Andy Smith <[EMAIL PROTECTED]> wrote: I have to get this down to < 1 sec. I
have 50 queries that are all
formatted similar. We have created another schema where the container
is split into smaller tables which might help in speeding this up. From
what I see the Left joins are killing the speed.
Here is what I am seeing when running explain query plan:
sqlite> explain query plan SELECT a.id, a.title, a.type, a.dateAdded,
a.url, a.path, a.containerID, a.mimeType, a.width, a.height, a.genreID,
a.thumbnailID, a.releaseYearID, a.artistID, w.title AS containerName,
x.title AS genreName, y.title AS releaseYearName, z.title AS artistName
FROM mediaitem AS a LEFT JOIN mediaitem AS b ON
b.containerID=a.containerID LEFT JOIN container AS w ON
w.id=a.containerID LEFT JOIN container AS x ON x.id=a.genreID LEFT JOIN
container AS y ON y.id=a.releaseYearID LEFT JOIN container AS z ON
z.id=a.artistID WHERE a.type=1 AND b.type=0 AND b.title LIKE 'Opus%'
ESCAPE '\' ORDER BY a.title, a.id LIMIT 0,9;
0|0|TABLE mediaitem AS a WITH INDEX mediaitem_title ORDER BY
1|1|TABLE mediaitem AS b WITH INDEX mediaitem_containerID
2|2|TABLE container AS w USING PRIMARY KEY
3|3|TABLE container AS x USING PRIMARY KEY
4|4|TABLE container AS y USING PRIMARY KEY
5|5|TABLE container AS z USING PRIMARY KEY
Schema:
sqlite> .schema
CREATE TABLE "container" (
-- fundamental container information
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"title" VARCHAR(255) NOT NULL,
-- enumerations
-- 10: music album
-- 11: music artist
-- 12: music genre
-- 13: music composer
-- 14: music conductor
-- 15: release year
-- 16: photo roll
-- 17: photo album / slide show
-- 18: events
-- 19: keyword
-- 20: people
-- 21: places
-- 22: user-defined tag
-- 23: playlist
-- 24: video season
-- 25: video series
-- 26: channel
-- 27: director
-- 28: actor
-- 29: producer
-- 30: audio series
-- 31: media folder
"type" INTEGER NOT NULL,
"dateAdded" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
"dateCreated" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
-- generic information about the media container
"details" TEXT DEFAULT NULL,-- lyrics, plot, etc.
"detailsURI" TEXT DEFAULT NULL,-- lyrics, plot, etc.
-- non-NULL only for slideshows and playlists
"duration" INTEGER UNSIGNED
DEFAULT NULL,-- in seconds
"transition" tinyint DEFAULT '0', -- 0: no effect
-- 1: fade in/out
-- non-NULL only for media folders
"upnpShared" tinyint DEFAULT '0',
"parentalLocked" tinyint DEFAULT '0',
-- voting and ratings
"rating" BIGINT UNSIGNED
DEFAULT NULL,
"voteCount" INTEGER UNSIGNED
DEFAULT NULL,
"avgRating" FLOAT DEFAULT NULL,
-- housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
"locked" tinyint DEFAULT '0',
"extended" tinyint DEFAULT '0'
);
CREATE TABLE "containeraux" (
-- used for certain types of containers to ensure title/type uniqueness
"title" VARCHAR(255) NOT NULL REFERENCES "container(title)",
"type" INTEGER NOT NULL REFERENCES "container(type)"
);
CREATE TABLE "extend" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"title" VARCHAR(255) NOT NULL,
"document" TEXT NOT NULL,
"contact" VARCHAR(255) NOT NULL,
"dateAdded" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
-- housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL
);
CREATE TABLE "extension" (
-- fundamental extension information
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"extendID" INTEGER NOT NULL REFERENCES "extend(id)",
-- exactly one of the following is non-NULL
"mediaitemID" INTEGER DEFAULT NULL REFERENCES
"mediaitem(id)",
"containerID" INTEGER DEFAULT NULL REFERENCES
"container(id)",
"keyvalue" VARCHAR(255) NOT NULL,
"valvalue" VARCHAR(255) NOT NULL,
"dateAdded" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
-- housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL
);
CREATE TABLE "mediaitem" (
-- fundamental mediaitem information
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"title" VARCHAR(255) NOT NULL,
-- enumerations
-- 0: audio
-- 1: image
-- 2: video
"type" INTEGER NOT NULL,
"dateAdded" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
-- remote access
"url" TEXT NOT NULL,
-- information about source
"origin" TEXT NOT NULL,
"path" TEXT NOT NULL,
"removable" tinyint DEFAULT '0',
-- identity of the containing object
"containerID" INTEGER NOT NULL REFERENCES
"container(id)",
"containerPOS" UNSIGNED INTEGER
DEFAULT NULL,
"containerDisc" UNSIGNED INTEGER
DEFAULT NULL,
-- generic information about the media item
"mimeType" VARCHAR(255) NOT NULL, -- e.g., image/jpeg
"size" BIGINT UNSIGNED
NOT NULL,
"details" TEXT DEFAULT NULL,-- lyrics, plot, etc.
"detailsURI" TEXT DEFAULT NULL,-- lyrics, plot, etc.
"genreID" INTEGER DEFAULT NULL REFERENCES
"container(id)",
"thumbnailID" INTEGER DEFAULT NULL REFERENCES
"mediaitem(id)",
"thumb320ID" INTEGER DEFAULT NULL REFERENCES
"mediaitem(id)",
"releaseYearID" INTEGER DEFAULT NULL REFERENCES
"container(id)",
-- non-NULL only for audio/video media items
"duration" INTEGER UNSIGNED
DEFAULT NULL,-- in seconds
-- non-NULL only for image/video media items
"width" INTEGER UNSIGNED
DEFAULT NULL,
"height" INTEGER UNSIGNED
DEFAULT NULL,
"colorDepth" INTEGER UNSIGNED
DEFAULT NULL,
-- non-NULL only for audio media items
"artistID" INTEGER DEFAULT NULL REFERENCES
"container(id)",
"bitRate" INTEGER UNSIGNED
DEFAULT NULL,
"sampleRate" INTEGER UNSIGNED
DEFAULT NULL,
"channels" INTEGER UNSIGNED
DEFAULT NULL,
"vbr" tinyint DEFAULT NULL,
"albumGain" tinyint DEFAULT NULL,
"albumType" VARCHAR(255) DEFAULT NULL,
"band" VARCHAR(255) DEFAULT NULL,
"bitDepth" INTEGER UNSIGNED
DEFAULT NULL,
"beatsPerMinute" INTEGER UNSIGNED
DEFAULT NULL,
"completeAlbum" tinyint DEFAULT NULL,
"compression" VARCHAR(255) DEFAULT NULL,
"intensity" INTEGER UNSIGNED
DEFAULT NULL,
"mixAlbum" tinyint DEFAULT NULL,
"peakLevel" INTEGER UNSIGNED
DEFAULT NULL,
"replayGain" INTEGER UNSIGNED
DEFAULT NULL,
-- non-NULL only for image media items
"aperture" INTEGER UNSIGNED
DEFAULT NULL,
"badPixels" tinyint DEFAULT NULL,
"camera" VARCHAR(255) DEFAULT NULL,
"caption" VARCHAR(255) DEFAULT NULL,
"flash" tinyint DEFAULT NULL,
"focalLength" INTEGER UNSIGNED
DEFAULT NULL,
"isoNumber" VARCHAR(255) DEFAULT NULL,
"rotation" INTEGER UNSIGNED
DEFAULT NULL,
"shutterSpeed" INTEGER UNSIGNED
DEFAULT NULL,
-- non-NULL only for video media items
"mpaaRating" VARCHAR(5) DEFAULT NULL, -- one of:
-- G, PG, PG-13, NC-17,
R, or NR
-- historic: G, GP, M,
or X
"mpaaReasons" VARCHAR(7) DEFAULT NULL, -- any of:
-- V, S, L, and/or D
"program" VARCHAR(255) DEFAULT NULL,
"episode" VARCHAR(255) DEFAULT NULL,
-- voting and ratings
"rating" BIGINT UNSIGNED
DEFAULT NULL,
"voteCount" INTEGER UNSIGNED
DEFAULT NULL,
"avgRating" FLOAT DEFAULT NULL,
-- ownership, rights, etc.
"copyright" TEXT DEFAULT NULL,
-- finally, housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
"locked" tinyint DEFAULT '0',
"extended" tinyint DEFAULT '0'
);
CREATE TABLE "tag" (
-- fundamental tag information
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
-- only one of mediaitemID or containerID is non-NULL
-- containerID may be non-NULL, ONLY:
-- IF the parentID's type is "user-defined tag"
-- AND the containerID's type is not "user-defined tag"
-- OR
-- IF the parentID's type is "media folder"
-- AND the containerID's type is also "media folder"
-- OR
-- IF the parentID's type is "music artist"
-- AND the containerID's type is "music album"
"mediaitemID" INTEGER DEFAULT NULL REFERENCES
"mediaitem(id)",
"containerID" INTEGER DEFAULT NULL REFERENCES
"container(id)",
"parentID" INTEGER NOT NULL REFERENCES "container(id)",
"parentPOS" UNSIGNED INTEGER
DEFAULT NULL,
"dateAdded" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
-- housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL
);
CREATE TABLE "user" (
-- fundamental userinformation
"userid" INTEGER PRIMARY KEY AUTOINCREMENT
-- other user-specific attributes go here...
);
CREATE TABLE "usercontainer" (
-- fundamental usercontainer information
"containerID" INTEGER NOT NULL REFERENCES "container(id)",
"userID" INTEGER NOT NULL REFERENCES "user(userid)",
-- user-specific container information
"notes" TEXT DEFAULT NULL,-- user-supplied
"comments" TEXT DEFAULT NULL,
"dateLastPlayed" TIMESTAMP DEFAULT NULL,
"lastPlayedPosition" BIGINT UNSIGNED
DEFAULT NULL,-- in
hundredths-of-a-second
"playCount" INTEGER UNSIGNED
DEFAULT NULL,
"dateLastSkipped" TIMESTAMP DEFAULT NULL,
"skipCount" INTEGER UNSIGNED
DEFAULT NULL,
"rating" INTEGER DEFAULT NULL, -- range: 1..5
-- bookmark information
"parentPOS" UNSIGNED INTEGER -- identifies position of
media
DEFAULT NULL,-- item having bookmark
"bookmark" BIGINT UNSIGNED
DEFAULT NULL,-- in
hundredths-of-a-second
"useBookmark" tinyint DEFAULT NULL,
-- housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL
);
CREATE TABLE "usermediaitem" (
-- fundamental usermediaitem information
"mediaitemID" INTEGER NOT NULL REFERENCES "mediaitem(id)",
"userID" INTEGER NOT NULL REFERENCES "user(userid)",
-- user-specific media item information
"notes" TEXT DEFAULT NULL,-- user-supplied
"comments" TEXT DEFAULT NULL,
"dateLastPlayed" TIMESTAMP DEFAULT NULL,
"lastPlayedPosition" BIGINT UNSIGNED
DEFAULT NULL,-- in
hundredths-of-a-second
"playCount" INTEGER UNSIGNED
DEFAULT NULL,
"dateLastSkipped" TIMESTAMP DEFAULT NULL,
"skipCount" INTEGER UNSIGNED
DEFAULT NULL,
"rating" INTEGER DEFAULT NULL, -- range: 1..5
-- non-NULL only for audio/video media items
"bookmark" BIGINT UNSIGNED
DEFAULT NULL,-- in
hundredths-of-a-second
"useBookmark" tinyint DEFAULT NULL,
-- housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL
);
CREATE UNIQUE INDEX "auxid" ON containeraux(title,type);
CREATE INDEX container_avgRating ON container(avgRating);
CREATE INDEX container_dateAdded ON container(dateAdded);
CREATE INDEX container_id ON container(id);
CREATE INDEX container_title ON container(title);
CREATE INDEX container_type ON container(type);
CREATE INDEX extend_contact ON extend(contact);
CREATE INDEX extend_title ON extend(title);
CREATE INDEX extension_containerID ON extension(containerID);
CREATE INDEX extension_extendID ON extension(extendID);
CREATE INDEX extension_keyvalue ON extension(keyvalue);
CREATE INDEX extension_mediaitemID ON extension(mediaitemID);
CREATE INDEX mediaitem_artistID ON mediaitem(artistID);
CREATE INDEX mediaitem_avgRating ON mediaitem(avgRating);
CREATE INDEX mediaitem_containerID ON mediaitem(containerID);
CREATE INDEX mediaitem_dateAdded ON mediaitem(dateAdded);
CREATE INDEX mediaitem_genreID ON mediaitem(genreID);
CREATE INDEX mediaitem_mpaaRating ON mediaitem(mpaaRating);
CREATE INDEX mediaitem_releaseYearID ON mediaitem(releaseYearID);
CREATE INDEX mediaitem_thumb320ID ON mediaitem(thumb320ID);
CREATE INDEX mediaitem_thumbnailID ON mediaitem(thumbnailID);
CREATE INDEX mediaitem_title ON mediaitem(title);
CREATE INDEX mediaitem_type ON mediaitem(type);
CREATE INDEX tag_containerID ON tag(containerID);
CREATE INDEX tag_mediaitemID ON tag(mediaitemID);
CREATE INDEX tag_parentID ON tag(parentID);
CREATE UNIQUE INDEX "ucid" ON
usercontainer(containerID,userID);
CREATE UNIQUE INDEX "ulid" ON
usermediaitem(mediaitemID,userID);
CREATE INDEX usercontainer_containerID ON usercontainer(containerID);
CREATE INDEX usercontainer_userid ON usercontainer(userid);
CREATE INDEX usermediaitem_mediaitemID ON usermediaitem(mediaitemID);
CREATE INDEX usermediaitem_userid ON usermediaitem(userid);
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote
Sent: Wednesday, April 09, 2008 11:25 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Left Join help
Andy Smith wrote:
> I have quiet a few queries similar to this doing multiple Left Joins
and
> they run extremely slow > 6 secs. Is there a better way to be writing
> the below query for sqlite.
>
Andy,
You didn't show your table and index definitions, so it's hard to be
sure about what would be best.
The following trace shows what I think your tables should look like, and
a couple of indexes that will help for a slightly rearranged version of
your query. The query plan uses the indexes to locate the subset of the
records with type=0, then checks their title, next it uses the other
index to find the matching records and checks that their type=1. These
records should lead directly to the required container records using the
primary key on that table. I would suspect this is reasonably fast. Let
me know if it helps.
HTH
Dennis Cote
SQLite version 3.5.7
Enter ".help" for instructions
sqlite>
sqlite> create table container (
...> id integer primary key,
...> title text
...> );
sqlite>
sqlite> create table mediaitem (
...> id integer primary key,
...> title text,
...> type integer,
...> containerID integer references container,
...> genreID integer references container,
...> thumbnailID integer references container,
...> releaseYearID integer references container,
...> artistID integer references container,
...> dateAdded,
...> url,
...> path,
...> mimeType,
...> width,
...> height
...> );
sqlite>
sqlite> create index media_type on mediaitem(type);
sqlite> create index media_container on mediaitem(containerID);
sqlite>
sqlite> explain query plan
...> SELECT
...> a.id,
...> a.title,
...> a.type,
...> a.dateAdded,
...> a.url,
...> a.path,
...> a.containerID,
...> a.mimeType,
...> a.width,
...> a.height,
...> a.genreID,
...> a.thumbnailID,
...> a.releaseYearID,
...> a.artistID,
...> w.title AS containerName,
...> x.title AS genreName,
...> y.title AS releaseYearName,
...> z.title AS artistName
...> FROM mediaitem AS b
...> LEFT JOIN mediaitem AS a ON b.containerID=a.containerID
...> LEFT JOIN container AS w ON w.id=a.containerID
...> LEFT JOIN container AS x ON x.id=a.genreID
...> LEFT JOIN container AS y ON y.id=a.releaseYearID
...> LEFT JOIN container AS z ON z.id=a.artistID
...> WHERE b.title LIKE 'Opus%' ESCAPE '\'
...> AND b.type=0
...> AND a.type=1
...> ORDER BY a.title, a.id
...> LIMIT 0,9;
0|0|TABLE mediaitem AS b WITH INDEX media_type
1|1|TABLE mediaitem AS a WITH INDEX media_container
2|2|TABLE container AS w USING PRIMARY KEY
3|3|TABLE container AS x USING PRIMARY KEY
4|4|TABLE container AS y USING PRIMARY KEY
5|5|TABLE container AS z USING PRIMARY KEY
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
This message is confidential to Prodea Systems, Inc unless otherwise indicated
or apparent from its nature. This message is directed to the intended recipient
only, who may be readily determined by the sender of this message and its
contents. If the reader of this message is not the intended recipient, or an
employee or agent responsible for delivering this message to the intended
recipient:(a)any dissemination or copying of this message is strictly
prohibited; and(b)immediately notify the sender by return message and destroy
any copies of this message in any form(electronic, paper or otherwise) that you
have.The delivery of this message and its information is neither intended to be
nor constitutes a disclosure or waiver of any trade secrets, intellectual
property, attorney work product, or attorney-client communications. The
authority of the individual sending this message to legally bind Prodea Systems
is neither apparent nor implied,and must be independently verified.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
