while this topic is 2 weeks old, I'd like to reply to it.
Quote:
|
First of all, queries will be simple and my main criteria is speed, concurrency lies on very bottom since only one person will access stored data at a time, for this purpose SQLite is faster because does not suffer from client-server latency - right?
|
As long as SQLite is running at the server (which is the case), it will be affected by server latency!
Quote:
|
I was reading on MySQL site that some sites using this database got over 50.000 of tables. In my system each account will have at least 10 tables. If my site will get say 100.000 accounts at some point, it makes 1 mil of tables. It makes sense to me to use SQLite and create separate directory for each user to store data, how would MySQL handle it in the best way? One db per user or one db for all with hundred thousands of tables? Any more options?
|
The reason why RDBMS were created was to make data centerlization easier so why each account has its own DB? it defies the purpose of RDMBS! make all your accounts access one database and design your database so it can handle more than one user and unify the tables as well so you don't create new tables for the same purpose with the same structure. one of the adventages of doing so is imagine if you wanted to make a simple database structure modification and you had 1000 users, you will do that modification once on one database rather that doing it on 1000 database which would be a nightmare and if your databases were large it will make the situation worse.
Quote:
|
As said, every account requires at least 10 tables. In case of SQLite would it be smart or dumb to keep each table in separate file? If something goes wrong, it'll affect only one table/file plus smaller files shall be quicker to process (hmmm not sure).
|
while therotically possible, programming it so is horrible and once your number of databases becomes large (and assume you have 1000 DBs), it becomes the server's file system responsibility to handle connections to those databases which can't be good. in the other hand, all RDMBS were created to handle a good number of connections at the same time.
Quote:
|
I nearly choose to use SQLite because is fast, simple and stores data in nice flat files for easy backup and migration, yet I'm not sure due to lack knowledge and experience basically.
|
the flat file easy back up idea might seem so appealing, but if you have 1 table per db, 1 db per user,10 tables per user, 1000 users.. that's 10,000 db files! that's messy!
all in all, if your application has the potential of growing to 10,000 users at one point, then concurrency & server load is important, and thus, I'd say MySQL could be a better choice. keep in mind that all performance-wise observations are theoritical and based on my experince not benchmarking or solid facts.
Linear Mode
