Performance boost by adding indexes to the SQLite tables

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

Performance boost by adding indexes to the SQLite tables

jfroebe
This post was updated on .
I was able to increase the responsiveness on the plugin by adding two indexes \Users\Public\RecordedTVHD\database\RecordedTVHDdb.s3db :

CREATE INDEX "MCID_Season_Ep_idx" on episodes (MCID ASC, SeasonNumber ASC, EpisodeNumber ASC)
CREATE UNIQUE INDEX "MCID_TVDBID" on series (TVDBID ASC, MCID ASC)

I'm not sure if the windows service vacuums or updates the index stats or not.  

Would love to see the location of the two databases configurable.

version 1.7.3
Reply | Threaded
Open this post in threaded view
|

Re: Performance boost by adding indexes to the SQLite tables

MJT Software
Administrator

Thank you for the info. I currently do a vacuum command when the service starts, but have not done any indexes. I will look into this ASAP.

Thanks

Reply | Threaded
Open this post in threaded view
|

Re: Performance boost by adding indexes to the SQLite tables

chrisdfw
This would speed it up and from a Database perspective makes sense
Reply | Threaded
Open this post in threaded view
|

Re: Performance boost by adding indexes to the SQLite tables

MoRBiD MaNGLeR
This post was updated on .
In reply to this post by jfroebe
jfroebe definitely has the right idea about indexing the database.

I tested this with Beta 1.7.9, and I saw a huge performance boost for my Movies list.  My Movies list is about 25 pages long in List View, and it populated in a few seconds instead of around 10 seconds.  Good find!

I applied an index to MyFutureRecordings.s3db in an attempt to lessen the load time of the Scheduled recordings.  Normally, it takes eight seconds for it to display to me.  The index took it down to six seconds.  It wasn't as noticeable as the Movies, but it was still faster nonetheless.

MoRBiD
Reply | Threaded
Open this post in threaded view
|

Re: Performance boost by adding indexes to the SQLite tables

schottjy
Can one of you provide instructions to try this for the non-DBAs on the forum?
Reply | Threaded
Open this post in threaded view
|

Re: Performance boost by adding indexes to the SQLite tables

MJT Software
Administrator
I've just updated the beta to include indexing:

Latest Beta Info and Download
Reply | Threaded
Open this post in threaded view
|

Re: Performance boost by adding indexes to the SQLite tables

MoRBiD MaNGLeR
I noticed that jfroebe did this index but you didn't:
CREATE UNIQUE INDEX "MCID_TVDBID" on series (TVDBID ASC, MCID ASC)

It appears that the only index you did on the Series table was for Genre.

Is jfroebe's index not needed due to the existence of the other indexes that you created?  I'm new to indexes so I'm not judging or anything.  Just curious.

MoRBiD
Reply | Threaded
Open this post in threaded view
|

Re: Performance boost by adding indexes to the SQLite tables

MJT Software
Administrator
The TVDBID one isn't needed because I never had TVDBID in the where clause of the queries. One is missing though. Series_MCID is supposed to be there. I'll look and see why it isn't and will get back to you.
Reply | Threaded
Open this post in threaded view
|

Re: Performance boost by adding indexes to the SQLite tables

MoRBiD MaNGLeR
The Series_MCID index is there.  I was just asking because it indexed the Episodes table instead of the Series table.  But it probably doesn't matter which table is indexed because the performance appears to be the same when comparing the two.  Again, I was just curious.

Your Series_MCID index:
CREATE INDEX "Series_MCID" on Episodes (SeriesName ASC, MCID ASC)

jfroebe's index:
CREATE UNIQUE INDEX "MCID_TVDBID" on series (TVDBID ASC, MCID ASC)

MoRBiD
Reply | Threaded
Open this post in threaded view
|

Re: Performance boost by adding indexes to the SQLite tables

MJT Software
Administrator

It still should be on series.... but thanks.

Reply | Threaded
Open this post in threaded view
|

Re: Performance boost by adding indexes to the SQLite tables

MoRBiD MaNGLeR
Mark,

Is Vacuum now disabled?  Or does it Vacuum but the indexes are kept intact?  Have you enabled (or considered?) Auto Vacuum?  I'm asking as a newbie, and I'm just curious as to how it's set up and configured.

MoRBiD
Reply | Threaded
Open this post in threaded view
|

Re: Performance boost by adding indexes to the SQLite tables

MJT Software
Administrator

It vacuums on service startup. Auto vacuum is bad for performance.... at least that is what I've learned online.

Reply | Threaded
Open this post in threaded view
|

Re: Performance boost by adding indexes to the SQLite tables

jfroebe

The indexes i proposed were based only on the schema not on the queries.  The schema isn't normalized but that is okay as the main activity is reads (selects) on a tiny amount of data. 

On Jul 21, 2012 4:23 PM, "MJT Software [via Recorded TV HD Forum]" <[hidden email]> wrote:

It vacuums on service startup. Auto vacuum is bad for performance.... at least that is what I've learned online.




To unsubscribe from Performance boost by adding indexes to the SQLite tables, click here.
NAML