Re: [Tracker] status of 0.6.90?

[Jamie McCracken <jamie mccrack googlemail com>]

> > > 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)

I would prefer keeping enabled in case other use cases for it crops up
as well 

> > 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.

I believe AuxilaryID is indexed so updating should be fast

searches on the other hand are more likely to be much more frequent

> 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


> 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


> > > Something else I have noticed. When it comes to deleting a file, it is
> > > really quite nasty because we have to delete in several places. It would
> > > be much nicer to delete the service id and that trigger deletes for
> > > every other place that relationally depends on it (like
> > > content/metadata/etc).
> >
> > triggers only work on the db connection that performs the deletion so
> > will not work on multiple dbs unless they are joined at the db
> > connection level
>
> You mean with ATTACH?

yes

> > we do use triggers in a few places but obviously the above makes it
> > harder
>
> Yea.