Wiki database design

What data do we haveEdit

  • article versions (lot of data)
  • uploaded files (lot of data)
  • user accounts (little data)

The interesting thing is that neither article versions nor uploaded files are ever modified - the new versions are uploaded, while the old stay in the archive.

Read operationsEdit

These are record-level:

  • get newest version of an article
  • get contents of newest version of some uploaded file
  • get specific version of an article
  • get history of an article
  • get diff between two versions of the article
  • get user preferences

These are not:

  • status of links
  • recentchanges
  • watchlist
  • search
  • list of articles that are longest/shortest/newest/orphaned/etc.
  • general statistics

While record-level read-only operations are going to be quite fast in any sane implementation, the others will need some special structures for reasonable performance.

Write operationsEdit

  • uploading new version of an article
  • uploading file
  • modifying user preferences

Acceleration of RecentChangesEdit

saneEdit

Special SQL table, just for that

insaneEdit

We could store RecentChanges as a in-memory table (recreated on database restart), using zero-locking queue.

Acceleration of WatchlistEdit

saneEdit

An index on creation time for versions.

insane 1Edit

One insane-RC-style queue for each user.

insane 2Edit

Store last requested version of each watchlist in cache. Use some smart updating when the watchlist is requested again in short time. Will improve performance mostly for people who use whatchlist frequently.

Acceleration of SearchEdit

sane 1Edit

Use MySQL FullText index.

sane 2Edit

Use external spider. No locking, but the data is going to be less up-to-date and we will have less control over the results.

sane 3Edit

Use Google. Even less work for us, and even less up-to-dateness and control.

insaneEdit

Hack MySQL replication (with binary log of changes) to allow searches being handled by separate MySQL process. Almost real-time ( delay measured in seconds) and no locking involved.

Acceleration of link tablesEdit

saneEdit

2 SQL tables:

  • links
  • brokenlinks