SQLite vs. MySQL

Ordix AG headlined an article about SQLite with the words: 'SQLite – The database for calorie-conscious'.

What is all in all correct. But why did I search information about SQLite? The answer is easy: I want to write an access wrapper for my new system, so I don't need to use mysql_query() all the time... And another benefit is that I would be able to change the whole database system, by just changing the class, the wrapper. And right form this idea away and by a friend I met SQLite, which sounds at the first time too small for a 'real' database. But it isn't.

By finding out about the new function in PHP5 sqlite_create_function() (http://www.php.net/manual/en/function.sqlite-create-function.php) I was sure that I'll use this database instead of MySQL. If I just find an Admin System like MyPhpAdmin for MySQL. I started researching information on this, and found right in the SQLite.org Wiki the answer: SqliteTools. I installed the tool called wtcSQLite and learn that what I just never understood before:

'SQLite just isn't a "real" database...'

Why? SQLite bases on flatfiles... So all data is saved in normal files, like your PHP, JPG or XHTML files. So, is that good or not? Well, it is the perfect combination of text files based 'databases' and 'database' databases :)) You know what I mean.

So what do you have to expect of it? It is perfect for those who don't get databases of their providers, or for those who save all their data (for news or contents) in text files, which ae close to the CSV standard. So those web-publishers will love this database. But is SQLite clever to use for those who have huge databases, like my Emma Watson-Website, with more than 25MB database contents just for the main page? (In my German and English boards together are managed over 60MB data by MySQL.)

In fact SQLite has its limits, and you will find in all sourses you ask for this database that SQLite is hardly conditioned to the number and volume of your transactions.

And as I'm going to write an Aggregator System the database I use must be able to handle thousands of data rows. I compare the system with HPANA, which manages about 20,000 news and about 19,000 users with their profiles. And my system is going to be even huger, because I'm going to write a content management for categories, based on versions. So every edit of a certain page will be saved as an external independent page. So each change can be cancelled.

As you can see would SQLite be the wrong database for my system. But I thought: By defining the transaction frame manually I can speed up the performance. This is true, by making this:

<?php
    // manuel transaction begin
    sqlite_query($db, "BEGIN");
    // SQL-Statements
    ...
    // SQL-Statements End
    // manuel transaction end
    sqlite_query($db, "COMMIT");
?>

There is one thing what makes the use of SQLite just impossible for me.

Every update of the database must be done by just one user.

What does this mean? SQLite isn't a multi-user database. An example: User #1 wants to change his profile in the same time as user #2 wants to add a comment for a news story. What happens? In case user #1 started the request a little before user #2 his request will be managed first and user #2 have to wait.

This isn't a problem if you have 'just' 100 users at the same time online. But if there are 500 and more browsing your site at the same time it can get a problem.

(And I'm nut a friend of the idea of making one table in an extra database... What could help a little.)

So after all I decided to keep the work with good old MySQL, which had some problems on my Emma Watson website, but just because of some mistakes in my script, which are removed now. And nevertheless SQLite is still in my head, maybe I'll start someday a page which is based on this system, but after I read an interesting tutorial about the power of MySQL, I'll use it for my next projects.