MySQL : 10 Million rows

[Anon]

Hello,

I would like to gather ideas and opinions on the best way to go about SELECTing from a MySQL DB containing 10 million records.

Here are some of my questions:

• What table type would be best ? InnoDB, MyISAM, etc...?

• Would it be better/faster to have many tables, joined by foreign keys, or a few tables in which the rows are much much bigger ?

• Would it be possible to make browsing 10 million rows (let's say 200 simultaneous connections) quick ?

• Any thoughts on how many replicated servers should I have for browsing and how many for UPDATES/DELETES

• What OS would be best to run this on ?

any other thoughts or suggestions are VERY welcome =)

--
Keith Bussey
[e-mail]
Tech. Project Coordinator - WISOL.com
[phone]

[tomhath]

I'd do a lot of prototyping first. Much depends on how the data will be accessed. Just a few different queries that are run with different selection criteria? You can optimize the tables and indexes for that.

Lots of updates? You're toast.

Consider going to a more expensive DBMS that give you stored procedures. Often the major players (Oracle, IBM, etc) will give you a "bailment copy" to use for prototyping. And plan on buying a LOT of memory for your really expensive database server.