Wiki database design
What data do we have[edit]
- 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 operations[edit]
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 operations[edit]
- uploading new version of an article
- uploading file
- modifying user preferences
Acceleration of RecentChanges[edit]
sane[edit]
Special SQL table, just for that
insane[edit]
We could store RecentChanges as a in-memory table (recreated on database restart), using zero-locking queue.
Acceleration of Watchlist[edit]
sane[edit]
An index on creation time for versions.
insane 1[edit]
One insane-RC-style queue for each user.
insane 2[edit]
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 Search[edit]
sane 1[edit]
Use MySQL FullText index.
sane 2[edit]
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 3[edit]
Use Google. Even less work for us, and even less up-to-dateness and control.
insane[edit]
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 tables[edit]
sane[edit]
2 SQL tables:
- links
- brokenlinks