Dennis,
With all the changes that got me down to 4.8 seconds. Better. We are
also trying a schema in which we break up the container so it's a bit
smaller. Now my other 54 queries run sub .5 secs except 1. Which is a
variant of the one I have asked for help on. Here is the new version on
a schema based on the container table being split apart.
explain query plan SELECT a.id, a.title, a.dateAdded, a.url, a.path,
a.containerID, a.mimeType, a.width, a.height, a.genreID, a.thumbnailID,
a.releaseYearID, a.artistID, (SELECT w.title AS containerName FROM
allAlbums AS w WHERE w.id=a.containerID), (SELECT x.title AS genreName
FROM allgenres AS x WHERE x.id=a.genreID), (SELECT y.title AS
releaseYearName FROM allreleaseYears AS y WHERE y.id=a.releaseYearID),
(SELECT z.title AS artistName FROM allartists AS z WHERE
z.id=a.artistID) FROM mediaImage AS a INNER JOIN mediaAudio AS b ON
b.containerID=a.containerID WHERE b.title LIKE 'Opus%' ESCAPE '\' ORDER
BY a.title, a.id LIMIT 0,9;
0|0|TABLE mediaImage AS a WITH INDEX mediaImage_title ORDER BY
1|1|TABLE mediaAudio AS b WITH INDEX mediaAudio_containerID
0|0|TABLE allAlbums AS w USING PRIMARY KEY
0|0|TABLE allgenres AS x USING PRIMARY KEY
0|0|TABLE allreleaseYears AS y USING PRIMARY KEY
0|0|TABLE allartists AS z USING PRIMARY KEY
This runs in about 2.9 seconds. Still way slower than needed. I am
really debating whether this can be sped up to meet the < 1 second goal.
Here is the schema for the alternate layout:
CREATE TABLE "allAlbums" (
-- fundamental information
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"title" VARCHAR(255) NOT NULL COLLATE NOCASE,
"dateAdded" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
-- housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE "allArtists" (
-- fundamental information
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"title" VARCHAR(255) NOT NULL COLLATE NOCASE,
"dateAdded" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
-- housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE "allGenres" (
-- fundamental information
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"title" VARCHAR(255) NOT NULL COLLATE NOCASE,
"dateAdded" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
-- housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE "allPlaylists" (
-- fundamental information
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"title" VARCHAR(255) NOT NULL COLLATE NOCASE,
"dateAdded" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
-- housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE "allReleaseYears" (
-- fundamental information
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"title" VARCHAR(255) NOT NULL COLLATE NOCASE,
"dateAdded" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
-- housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE "allTags" (
-- fundamental information
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"title" VARCHAR(255) NOT NULL COLLATE NOCASE,
"dateAdded" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL,
-- housekeeping...
"dateModified" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE "container" (
-- fundamental container information
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"title" VARCHAR(255) NOT NULL COLLATE NOCASE,
-- 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
"ownerID" INTEGER DEFAULT NULL REFERENCES
"user(userid)",
"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: cross fade
-- 2: fade to black
-- 3: slide
-- 4: zoom
-- 5: wipe
-- 6: iris wipe
-- 7: star wipe
-- 8: vertical blind
-- 9: horizontal blind
-- 10: random
-- 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)"
COLLATE NOCASE,
"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 "mediaAudio" (
-- fundamental information
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"title" VARCHAR(255) NOT NULL COLLATE NOCASE,
"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
"ownerID" INTEGER DEFAULT NULL REFERENCES
"user(userid)",
"mimeType" VARCHAR(255) NOT NULL, -- e.g., audio/mpeg
"size" BIGINT UNSIGNED
NOT NULL,
"details" TEXT DEFAULT NULL,-- lyrics, plot, etc.
"detailsURI" TEXT DEFAULT NULL,-- lyrics, plot, etc.
"location" TEXT DEFAULT NULL,-- e.g., where picture
taken
"genreID" INTEGER DEFAULT NULL REFERENCES
"container(id)",
"thumbnailID" INTEGER DEFAULT NULL REFERENCES
"mediaImage(id)",
"thumb320ID" INTEGER DEFAULT NULL REFERENCES
"mediaImage(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 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,
-- 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 "mediaImage" (
-- fundamental information
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"title" VARCHAR(255) NOT NULL COLLATE NOCASE,
"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
"ownerID" INTEGER DEFAULT NULL REFERENCES
"user(userid)",
"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.
"location" TEXT DEFAULT NULL,-- e.g., where picture
taken
"genreID" INTEGER DEFAULT NULL REFERENCES
"container(id)",
"thumbnailID" INTEGER DEFAULT NULL REFERENCES
"mediaImage(id)",
"thumb320ID" INTEGER DEFAULT NULL REFERENCES
"mediaImage(id)",
"releaseYearID" INTEGER DEFAULT NULL REFERENCES
"container(id)",
-- non-NULL only for audio media items
-- but included here for album art
"artistID" INTEGER DEFAULT NULL REFERENCES
"container(id)",
-- 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 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,
-- 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 "mediaVideo" (
-- fundamental information
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"title" VARCHAR(255) NOT NULL COLLATE NOCASE,
"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
"ownerID" INTEGER DEFAULT NULL REFERENCES
"user(userid)",
"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.
"location" TEXT DEFAULT NULL,-- e.g., where picture
taken
"genreID" INTEGER DEFAULT NULL REFERENCES
"container(id)",
"thumbnailID" INTEGER DEFAULT NULL REFERENCES
"mediaImage(id)",
"thumb320ID" INTEGER DEFAULT NULL REFERENCES
"mediaImage(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 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" (
-- this table is fully-defined elsewhere...
"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 "usermediaAudio" (
-- fundamental information
"mediaAudioID" INTEGER NOT NULL REFERENCES "mediaAudio(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 TABLE "usermediaImage" (
-- fundamental information
"mediaImageID" INTEGER NOT NULL REFERENCES "mediaImage(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 TABLE "usermediaVideo" (
-- fundamental information
"mediaVideoID" INTEGER NOT NULL REFERENCES "mediaVideo(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 INDEX allAlbums_dateAdded ON allAlbums(dateAdded);
CREATE INDEX allAlbums_title ON allAlbums(title);
CREATE INDEX allArtists_dateAdded ON allArtists(dateAdded);
CREATE INDEX allArtists_title ON allArtists(title);
CREATE INDEX allGenres_dateAdded ON allGenres(dateAdded);
CREATE INDEX allGenres_title ON allGenres(title);
CREATE INDEX allPlaylists_title ON allPlaylists(title);
CREATE INDEX allPlaylistss_dateAdded ON allPlaylists(dateAdded);
CREATE INDEX allReleaseYears_dateAdded ON allReleaseYears(dateAdded);
CREATE INDEX allReleaseYears_titl ON allReleaseYears(title);
CREATE INDEX allTags_dateAdded ON allTags(dateAdded);
CREATE INDEX allTags_title ON allTags(title);
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_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 mediaAudio_artistID ON mediaAudio(artistID);
CREATE INDEX mediaAudio_avgRating ON mediaAudio(avgRating);
CREATE INDEX mediaAudio_containerID ON mediaAudio(containerID);
CREATE INDEX mediaAudio_dateAdded ON mediaAudio(dateAdded);
CREATE INDEX mediaAudio_genreID ON mediaAudio(genreID);
CREATE INDEX mediaAudio_releaseYearID ON mediaAudio(releaseYearID);
CREATE INDEX mediaAudio_thumb320ID ON mediaAudio(thumb320ID);
CREATE INDEX mediaAudio_thumbnailID ON mediaAudio(thumbnailID);
CREATE INDEX mediaAudio_title ON mediaAudio(title);
CREATE INDEX mediaImage_artistID ON mediaImage(artistID);
CREATE INDEX mediaImage_avgRating ON mediaImage(avgRating);
CREATE INDEX mediaImage_containerID ON mediaImage(containerID);
CREATE INDEX mediaImage_dateAdded ON mediaImage(dateAdded);
CREATE INDEX mediaImage_genreID ON mediaImage(genreID);
CREATE INDEX mediaImage_releaseYearID ON mediaImage(releaseYearID);
CREATE INDEX mediaImage_thumb320ID ON mediaImage(thumb320ID);
CREATE INDEX mediaImage_thumbnailID ON mediaImage(thumbnailID);
CREATE INDEX mediaImage_title ON mediaImage(title);
CREATE INDEX mediaVideo_avgRating ON mediaVideo(avgRating);
CREATE INDEX mediaVideo_containerID ON mediaVideo(containerID);
CREATE INDEX mediaVideo_dateAdded ON mediaVideo(dateAdded);
CREATE INDEX mediaVideo_genreID ON mediaVideo(genreID);
CREATE INDEX mediaVideo_mpaaRating ON mediaVideo(mpaaRating);
CREATE INDEX mediaVideo_releaseYearID ON mediaVideo(releaseYearID);
CREATE INDEX mediaVideo_thumb320ID ON mediaVideo(thumb320ID);
CREATE INDEX mediaVideo_thumbnailID ON mediaVideo(thumbnailID);
CREATE INDEX mediaVideo_title ON mediaVideo(title);
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 INDEX usercontainer_containerID ON usercontainer(containerID);
CREATE INDEX usercontainer_userid ON usercontainer(userid);
CREATE UNIQUE INDEX "usermediaAudio_id" ON
usermediaAudio(mediaAudioID,userID);
CREATE INDEX usermediaAudio_mediaAudioID ON
usermediaAudio(mediaAudioID);
CREATE INDEX usermediaAudio_userid ON usermediaAudio(userid);
CREATE UNIQUE INDEX "usermediaImage_id" ON
usermediaImage(mediaImageID,userID);
CREATE INDEX usermediaImage_mediaImageID ON
usermediaImage(mediaImageID);
CREATE INDEX usermediaImage_userid ON usermediaImage(userid);
CREATE UNIQUE INDEX "usermediaVideo_id" ON
usermediaVideo(mediaVideoID,userID);
CREATE INDEX usermediaVideo_mediaVideoID ON
usermediaVideo(mediaVideoID);
CREATE INDEX usermediaVideo_userid ON usermediaVideo(userid);
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote
Sent: Wednesday, April 09, 2008 1:17 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Left Join help
Andy Smith 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.
>
I don't think that is your problem. You are doing a full table scan of a
large table. The last four left joins are required no matter what, but
they will be quick since they use the container primary key to get the
required record directly.
You want to use the most specific test you can to eliminate as many
records as possible early on. The problem is that your LIKE test can't
use an index as given. See http://www.sqlite.org/optoverview.html for
details. You need to make a few changes to your query to get it to use
the index on the mediaitem title for the like test.
First remove the unnecessary escape clause from the like clause. The set
the case_sensitive_like pragma on. Next drop the index mediaitem_type so
that sqlite will use the title index instead. Finally rearrange the
order of the a and b tables so the like test is applied first using the
index.
sqlite> drop index mediaitem_type;
sqlite> pragma case_sensitive_like = 1;
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%' --remove the escape clause
...> 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 mediaitem_title
1|1|TABLE mediaitem AS a 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
With these changes you will use the title index to quickly reduce the
table to only those records that match the title. For each of these
records it will check the type, then join the records with the same
containerID using that index with another check for the required type.
Finally it will select the indicated records from the container table
based on the rows selected.
HTH
Dennis Cote
_______________________________________________
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
