Advice on performance concerns (MySQL / Number of rows)

Hi all,

I'm developing a web app that I intend on trying to sell as SaaS
(Software as a Service (ie Basecamp)).

1) The first thought on my head would be to use one single database,
and create columns to indicate which client was the owner of that row.
The main table will be getting somewhere around 50 rows everyday for
each client. This gives me near 1k rows per month, per client
(considering only business days + rounding). Considering that I hope I
can reach say 100 clients, I will be getting 100k rows per month. In
one year, thats more than 1m rows. I believe that queries on tables
with 1m rows have a significant slowdown.

2) So the next thought was to create one database per client. Then I
would have 100 databases in one server, which does not sound that bad.
To be able to do this and still keep only one codebase, I would need
to store the database connection parameters in different files. Then
when someone requires data from xxxx client, xxxx_dbs.yml is used. Any
thoughts on that?

3) Also, I could create a mix of both options, where I would have
client groups (creating groups for every, say, 10 clients), then
having only 1/10th of the configuration files and 1/10th of the
databases, but requiring a column on each database to differentiate
between clients (this sounds hard to implement on app level).

At this point, option number 2 sounds like the best one. Considering
that my app actually gets useful and sells a lot, reaching, say, 5000
clients, will the option for 100 clients still be the best one for 5k
clients?

Do you know how 37signals works regarding this situation?

Thanks!

Hi all,

I'm developing a web app that I intend on trying to sell as SaaS
(Software as a Service (ie Basecamp)).

*snipped examples of shared vs. separate databases approach*

At this point, option number 2 sounds like the best one. Considering
that my app actually gets useful and sells a lot, reaching, say, 5000
clients, will the option for 100 clients still be the best one for 5k
clients?

See <http://msdn2.microsoft.com/en-us/library/aa479086.aspx>.

Isak