This is how the table will look like in the end:
2 billion rows, each row consists of 7 fields (3 VARCHAR, 4 INT). These rows are about 100 Bytes long, including two indices. So the whole table will consume about 200GB.
At the moment, I just use a test bed with about 20 million rows.
This table is used for a lot of read queries, which have to be answered very fast. I also have several UPDATEs, but they can be performed in batch mode, when there are no other queries.
So, does anyone have experiences with MySQL Merge tables, especially with the intent to speed up SELECTs in very big tables?
Do you know other ways to achieve this? I know the RAID-Option could be an interesting feature, but did not find to much information about it.
Are there any good books that deal with these advanced MySQL features?
I don't have a lot of advice to share here, but I've seen applications that work an very large datasets with speed of SELECTs being critical (i.e. search engines that store index in MySQL). They used much more sophisticated schemes to speed up access than Merge tables.
The rule of thumb is to try to make table(s) that are searched as small as possible with a fixed row length. How to do it exactly depends on querying patterns of your application. By the way, MySQL 4.x has a nice internal cache to help you.
As far as MySQL books go, they all seem to cover 3.x versions - now obsolete. But the latest online documentation is very comprehensive and you can order it in book form from MySQL site.
