Mysql, SQLite, Scalability
SQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites). The amount of web traffic that SQLite can handle depends, of course, on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.
The short answer is: SQLite is embedded database. It is purpose is different than standalone RBDMS. While it is quicker with simple queries than MySQL, keep in mind that SQLite has:
- no good networking support (SQLite purpose is different), so replication is PITA
- coarse-grained locking (one write at a time)
- no advanced table statistics
- no sophisticated query optimizer
- high memory consumption with large databases (a 100GB database would require about 25MB or RAM before each transaction)
Then if you do not plan to use SQLite over network, database sizes are quite small, queries are rather simple, and you have a lots of reads (and really small number of writes), then SQLite may be a better choice.
About MySQL: optimizing and using MySQL in super high traffic sites is not for faint hearted. I recommend some good reading: