Erik Moeller wrote:

Merging the tables is certainly undesirable as this would substantially slow things down (the OLD table of the English wiki is currently 8801714176 bytes and dog slow).

Are you sure the size makes it slow? The way I understand it, it doesn't; if you split it up into several tables, it'll still have to organise those different tables. Either way, a row look-up will be O(n) without an index and O(log n) with an index.

What makes a database slow is the number of reads and writes it has to perform (mainly the writes because they block the reads).

So, if my perception of this all is correct, the best way to optimise things is to use efficient queries (trying to minimise writes, especially on the most heavily read-from tables) and things like MemCacheD (which heavily reduces the number of reads), and not to split things up into several tables.

Greetings, Timwi

Erik Moeller wrote:

Timwi-

...

Are you sure the size makes it slow? The way I understand it, it doesn't; if you split it up into several tables, it'll still have to organise those different tables.

It certainly will, but it won't have to perform, e.g., a fulltext search on the entire index, but only on the index for that table.

First of all, I have to admit I was was being short-sighted again by forgetting to consider fulltext searches. My apologies.

However, I'm not convinced that this really matters. It would only have to search on the entire fulltext index if you actually wanted to search the entire table (an inter-language search). If you had an integer column that specifies the language of an article, and an index on that, then searching only the English text would be equal in speed to having a separate table for the English text.

At least this is how I understand it. Of course I may well be wrong. I'll ask a MySQL expert later on, when he's online :-)

Compare the fulltext search of the English wiki with the one on the German wiki -- that is certainly no O(log n) scalability.

No, certainly not. What I said about O(n) and O(log n) applies to selection of a single row from a simple key on a fixed-length column. What I was thinking of was combining the tables to form one whole Wikipedia database so we could have one single Watchlist and one inter-language Recent Changes page. Then generating an English-only Recent Changes page from the combined table should not be slower as it is now.

Timwi

Timwi wrote:

I have asked my friend and he confirmed by suspicion.

If you have several large tables A, B, C, ... etc. with identical columns and types (that could, for example, be the recentchanges tables for the different languages), you can always combine them into a big table with an extra (indexed) integer or other fixed-length column that specifies what original table the row came from (i.e. what language it's in), and it won't be any slower.

This is true in theory, and I believe true in some "big iron" production database systems (Oracle, etc.), but I'm not sure it's true in practice in many of the free DB systems. In particular, big tables and big indexes can mess up caching algorithms. In fact sometimes it seems to even be beneficial to split into entirely separate databases (not just separate tables in the same database). Why this is true I don't know enough about the internals of the various DB systems to say, but splitting seems to have resulted in a large speedup at kuro5hin.org, among other places that use MySQL.

-Mark