Re: [mb-devel] Track mod search speed improvement

On Tue, Feb 14, 2006 at 05:46:05PM +0100, Björn Krombholz wrote:
> > There is already an index (both for _open and _closed) on rowid.  It should 
> > be
> > using that, right?
> 
> It does not, that's the main problem.

That was a rhetorical question :-)

> And actually this index doesn't make much sense. Because one is never
> interested in a single rowid scan, because rowid is a mixture of ids of the
> normal entity tables, meaning it can refer to a track.id or an album.id or
> even an albumjoin.id.

It makes sense because whenever you looking up based on rowid, you always
specify one or more rowids.  Therefore, an index on rowid (only) should be
sufficient, because given some rowid "N", there will only be a few rows with
that rowid, and only a subset of those (where "table" also matches) are the
ones you're interested in.  It's a trade-off; a smaller and simpler index,
v.s. a more specific index.

> This doesn't really work, because the SELECT id FROM albumjoin could
> return 20 or more results, although moving this nested query out and
> adding the resulting array manually would increase the speed.

This is exactly what I was suggesting.

-- 
Dave Evans

PGP key: http://rudolf.org.uk/pgpkey

_______________________________________________
MusicBrainz-devel mailing list
[EMAIL PROTECTED]
http://lists.musicbrainz.org/mailman/listinfo/musicbrainz-devel