Re: [Tracker] status of 0.6.90?

[Jamie McCracken <jamie mccrack googlemail com>]

On Tue, 2008-10-14 at 15:16 +0100, Martyn Russell wrote:
> Jamie McCracken wrote:
> > > > > One question I have is, do we really need the "enabled" column if it
> > > > > only pertains to the Volumes table - can we not just have 1 column and 1
> > > > > row in the Volumes table for "enabled" and do some nice SQL to know if
> > > > > content is enabled based on purely the auxiliary ID in the Services
> > > > > table. Or is the "enabled" column needed for other things.
> > > > for performance reasons and to simplify queries, the enabled column is a
> > > > must
> > > I don't think simplifying the queries holds any water. Speed should
> > > always be the most important consideration right?
> >
> > well yes and doing a join against volume table would slow down searches
> > (by how much I am not sure)
>
> Yes, I think that's a good point and JOINs really slow things down
> apparently.
>
> > I would prefer keeping enabled in case other use cases for it crops up
> > as well 
>
> OK.
>
> > > > its very easy to update (update services set enabled = 1 where
> > > > AuxiliaryID = blah)
> > > Updating (say 2000) MP3 items in a table of thousands to set them from
> > > enabled = 0 to enabled = 1 should not be faster than setting 1 column in
> > > 1 row in a table of maybe 10 entries surely?
> >
> > well that is only done when mounting/unmounting so frequency is very
> > low. If the mounted path is changed then you will have a big hit here in
> > any event.
>
> True.
>
> > I believe AuxilaryID is indexed so updating should be fast
> >
> > searches on the other hand are more likely to be much more frequent
>
> Yea.
>
> > > As for lookups, I am not entirely sure, but I would think if the SQL
> > > query was constructed properly it should be as fast to check with the
> > > Volumes table if the AuxiliaryID is enabled.
> >
> > it would still be a hit as you are joining against another table
> >
> > > It doesn't feel truly relational to have an AuxiliaryID and an Enabled
> > > field. Is the Enabled field used for anything else other than this right
> > > now?
> >
> > its not used at all currently but there may be use cases where you want
> > to temporarily disable certain search hits - EG you might want to hide a
> > particular file from searches which you cant currently do as we only
> > exclude patterns or directories atm int he indexing
>
> Fair enough.
>
> This sort of thing needs documenting at I think. I feels like there are
> a lot of fields in the Services table which are unused. This must impact
> performance.

possibly  - most frequently accessed fields should be at front of table
as field offsets are decoded sequentially. Having a load of unused
fields at end of table wont have much impact


> > > Also I notice we have a lot of SELECT DISTINCT SQL statements where the
> > > WHERE clause is the ID - which is unique - doesn't that negate the whole
> > > point of DISTINCT and just slow down the statement?
> >
> > distinct wont slow it down if only one row is ever returned but yeah
> > wont do any harm to remove distinct in those cases
>
> I was under the impression it does even if there is only 1 entry. I have
> removed it now. I guess it depends on if the distinction is done before
> or after all entries matching the WHERE clause is done.

I dont know but I would guess it would start checking for uniqueness
after the first row is retrieved 

jamie