This is my situation: (I'm using MySQL)

Which one should I use:

--------------------- Option #1:

TABLE structure:
- id (index)
- data

Data:
I have 100 different databases (clients), which each contains a table with 100 000 lines. I separate the clients simply by calling the database I need when I need.


--------------------- Option #2:

TABLE structure:
- clientId (index)
- id (index)
- data

Data:
I have 1 db with one table that contains 100 x 100 000 lines and uses index field ClientId to "separate" the rows.

---

If I have such a massive amount of information, should it be better to use different dbs or is it okay to use just one db (and clientId as primary index)?

Every time I call the database I will be fetching only the information for one specific client. There's no need to fetch info of client 1 and client 2 for example.

Second option is faster. MySQL will be able to handle it without any problem..

Is this a test question in a class? I've seen this one before.

http://www.webhostingtalk.com/showthread.php?s=&threadid=379025

100,000 records isn't that much. I agree with the above, sounds like a homework question.

Edit: rereading the question, it might be legit, but needs rewording...

poster runs a number of "databases" for clients, probably product data, each client has about 100,000 rows in this primary data table.

poster wants to know if this would be more efficient by storing all product data for all clients in a single table with 10,000,000 (10 million) rows instead of 100 tables with 100,000 rows.

Really this depends on the dataset, and the use of the dataset. 100,000 rows isn't much, but 10,000,000 rows is getting up there. In eiher case appropriatly designed indexes will be important.