Loading...

musicbrainz-devel@musicbrainz.org

[Prev] Thread [Next]  |  [Prev] Date [Next]

[mb-devel] Track mod search speed improvement Björn Krombholz Sat Aug 12 16:23:26 2006

Hi,

a normal query for mods of a track looks like this:

SELECT m.*, NOW()>m.expiretime AS expired, -3 AS vote FROM
moderation_open m WHERE ( (m.tab IN ('track', 'Track') AND m.rowid =
5374651) OR (m.tab IN ('albumjoin', 'AlbumJoin') AND m.rowid IN
(SELECT id FROM albumjoin WHERE track = 5374651) ) ) UNION ALL SELECT
m.*, NOW()>m.expiretime AS expired, -3 AS vote FROM moderation_closed
m WHERE ( (m.tab IN ('track', 'Track') AND m.rowid = 5374651) OR
(m.tab IN ('albumjoin', 'AlbumJoin') AND m.rowid IN (SELECT id FROM
albumjoin WHERE track = 5374651) ) ) ORDER BY 1 DESC;

It looks complicated but is necessary, the reason why it is so fscking
slow is a missing index.

Running this query locally, shows:
musicbrainz_db=# SELECT m.*, ...
...
Time: 29201.320 ms

So I'm always lucky when it doesn't timeout, when running such
searches via the web interface. ;)

Adding an index (that's usefull for othor old mods searches as well) like:
# CREATE INDEX moderation_closed_idx_tab_rowid ON moderation_closed
USING btree (tab, rowid);

results in a runtime of: 9116.054 ms with much less discio, because
without this index pgsql performs a complete sequential read of the
>1GB moderation_closed table, while it only has to read the much
smaller index if it is there.

Repeating a similar search, while the index is still in linux' disk
caches needs only 140.642 ms!

That's nearly 200 times faster than without the index, because the
moderation_closed table is too big to ever be completely cached.

I think it's worth to add this index to the next release as we will
soon reach a state where such queries will always timeout. I have no
idea how such schema changes are handled for server upgrades, so I
leave it to you, rob or dave (?) to do the small change. (only incase
you agree with me, of course). ;)


#Fuchs

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