If you’re trying to decide between SQLite and mySQL (which one to use as your DBMS), you must first figure out what you need. These few simple questions should point you in the right direction:
- Do you have a storage space limit?
- The SQLite library is a mere 250kb, which is perfect for most embeded devices that don’t come with very much storage space.
Do you need the database to be portable?
- SQLite stores the database directly into a single file, which can be simply copied or moved.
- mySQL has an export feature which lets you back the database into a single file. mySQL uses its own format for storing data so the content can’t be moved or viewed by other DMBSs. Only after the database is exported will it be portable. Depending on how large the database is, this could be time consuming.
Do you need security or authentication for your database?
- SQLite does not provide an authentication system. You can add one within your application, however the database file itself can be updated/read by anyone.
- mySQL requires a username and password to make any kind of changes to the database.
How large will your database get in the future?
- SQLite requires too much memory to run if the database is over 1GB in size (256 bytes of RAM for each MB of database space).
- mySQL can have a maximum database size of 4GB.
- Will the database be accessed multiple times simultaneously?
- SQLite does not handle multiple simultaneous queries well. All read operations have priority over the write operations. There is a small delay caused by this since the operations must wait their turn, which could become a problem if there are large numbers of simultaneous queries.
- mySQL has a query mechanism which handles mutiple query operations very well.
Hopefully your basic doubts about SQLite and mySQL have now been answered.