CODEWITHSUNDEEP
phpmysqlsqlite
Mitchell V
Mitchell V

Reputation: 887

Individual SQLite Databases per User over MySQL?

Starting a new project here and I'm going to be storing a LOT of user data. I'm trying to make the system scalable from the start so I'm considering the fairly new idea of storing each user's data (essentially lists of files they have stored) in separate SQLite databases stored in a user-specific directory, rather than one huge table in MySQL with user IDs. The file list will store other metadata related to the file so just using the filesystem isn't an option.

My thought there is that when users are logged in and viewing their files it will be faster to just show all data in a single SQLite database rather than have MySQL go over all records in the "file" table to pull out one user's file by ID. Each user will easily have 10,000+ entries and there will initially be at least 400 users. So 400 individual SQLite databases with 10,000 rows, or a single MySQL table with 4 million? Keeping in mind that all 400 users will rarely (if ever) be logged in at the same time it seems inefficient to have the database have to deal with data for users that aren't there, even if it is indexed.

The single biggest restriction of SQLite is the locking but luckily in this case there will only ever be a single process writing to the database so that shouldn't be a problem here. The added administration of backing up the individual SQLite databases is trivial since they're all going to be part of the incremental filesystem backup anyway.

Thoughts? Opinions? Am I over thinking this?

Upvotes: 1

Views: 2086

Answers (4)

Clifton Labrum
Clifton Labrum

Reputation: 14128

I have been doing this very thing with an app I built. Each user has their own SQLite database. My reason for this is because I allow the user to back up their data to Dropbox, as well as sync it to a matching SQLite database in an iOS app (via Dropbox).

Pros:
- User data is isolated, and it's easy to do support and look at a single database at once.
- The data is ready to push to Dropbox at any moment. It's readily portable.

Cons:
- As has been stated, schema changes are a pain. I have to keep a table in each SQLite db called "version" and track a version number that represents what their schema is like. Schema changes involve writing gnarly alteration queries and iterating over all the database files stored on my server.
- It's likely that the server bears a heavier load opening so many separate databases. I suspect an indexed MySQL database would be faster.

Overall, it works, but I'm actually looking to consolidate all the data into a single MySQL database because of the schema change woes.

Good luck.

Upvotes: 2

Mechcozmo
Mechcozmo

Reputation: 303

I'd imagine this won't scale well at all. Let's say you outgrow your current server and attempt to bring up a second database server. Which one has which user IDs? MySQL's master/slave replication means you don't have to worry about that problem.

I'd also think that the OS you are running this on will have a difficult time keeping all those files around in an optimized manner. With MySQL, you can optimize a table easily, taking care of fragmentation and whatnot. If you go with SQLite, you may spend more resources seeking and loading files memory than MySQL would seeking to that part of the table.

It's an interesting thought experiment, but I don't think it will do what you are looking for. If you really want to make something scalable and fast, try looking at MongoDB: http://www.mongodb.org/. It's fast, flexible, and scales well. Plus, you could keep a listing of files and search for which user uploaded them- something that might be useful if you want to do a Dropbox-like 'only store files we haven't seen before' thing.

Upvotes: 0

Emil Vikström
Emil Vikström

Reputation: 91983

I think it will be much more expensive to open and close thousands of database files (SQLite), compared to querying a single server instance which have recent databases and tables open already (MySQL).

400 users is not either. You will probably not see any performance problems at all at those levels.

Upvotes: 1

ceejayoz
ceejayoz

Reputation: 180095

My thought there is that when users are logged in and viewing their files it will be faster to just show all data in a single SQLite database rather than have MySQL go over all records in the "file" table to pull out one user's file by ID.

No, not if you index the MySQL database properly. 4 million records should present no trouble if you've got things set up correctly.

Upvotes: 2

Related Questions