I'm working on coding a CMS for my site, and I'm wondering which database technology to use. SQLite by all reports appears to be the faster, and as an SQLite database is a ".db" file, backing up is a ton easier. I've also had more luck getting stuff working with SQLite.
On the other hand, mySQL is more powerful (from what I can tell) and more widely used (until php5 becomes completely stable -- it is included in the package for php5)
So. . .which should I use? Which is better?
I'd be interested to see some comparisons between SQLite and MySQL. I'd imagine that MySQL would win out on heavier queries but I'd like to be proved wrong. I don't think either are really good for 'serious' applications though.
What do you mean "neither are good for serious applications"? Access is so amazingly slow it's terrible, and PostGreSQL is also really slow. What's better for "serious" applications" (There are many serious -- as in complicated, well done, and indeed commercial [IPB] apps done with MySQL).
But. .. my question of which to use for my cms is still unaswered 
Owners of mission-critical applications are more likely to turn to commercial products like Oracle, SQLServer etc to ensure reliability, performance, support and functionality.
Here are some interesting stats though:
Cox Communications uses MySQL to manage information related to its cable modem business.
NASA uses MySQL to store information about public contracts.
Slashdot, a widely read online publication, uses MySQL to store all of the information related to its site.
The Associated Press uses MySQL to serve various types of information, including access to the U.S. Census and Olympic results.
Afilias, which manages the .ORG registration, uses PostgreSQL to store all of .ORG registry information.
The American Chemical Society uses PostgreSQL to store documents that exist only within that database.
BASF uses PostgreSQL in a shopping platform for its agriculture products.
The World, a media company, has built much of its infrastructure around the use of PostgreSQL.
Cool!
But does anyone have any advice on whether, for my light-weight small cms system I should use SQLite or MySQL?
well, me personally i would prob use mysql because is much more mature and widely supported.
but if this is for a personal cms you dont plan to relase publically, and you dont have an issue w/ finding support for sqlite, sqlite sounds very attractive.
its hard for anyone here to say whats best for you. both are very capable databases, and both have pros and cons.
sqlite seems like the better choice for what you will prob use it for, the main drawback i see is support.
Yeah. . .see, once PHP 5 is fully mature and the server standard, there'll be no prob getting SQLite because it's included in PHP. However, at this point, it's a lot harder to find hosting with SQLite.
So perhaps I'll do it with MySQL. . .but I really like SQLite. . .hmm. . .hard decision
Well, in the end it will boil down to what is on your host server. If it's a commercial web host that you are going to use then mysql will make your code portable when you get sick of the host.
Re: the 'serious' application issue. It is horses for courses. Mysql is the best for a web backend - it is what it was built for, and will outperform almost any other db on a matching box. However, web backends are generarly seen as data relivery systems with not a lot of real-time updates but loads of concurrrent selects. If it is infact production system with lots of complex transactions that you are developing using web tools, then mysql is not the right db. For a that kind of production system you need a db that was developed for the job, like oracle, sqlserver, postgrsql, etc.
PS Access is only slow if you don't know how to use it, and of course because it has to go on an NT box, and of course because it is not built for a web environment. Nothing slow about my backoffice dbs on our LAN, but then I know how to use it.
Serious being 100GB of data or more.
Originally posted by rehfeld
well, me personally i would prob use mysql because is much more mature and widely supported.
I don't know about "mature" - only now is it getting features that other DBMSs have had for years. And for most of its history it's lacked even basic facilities. And it's still wackily non-compliant.
Yes. Like PHP, it does not conform to various 'standards', but then it was developed by web programmers for web programmers as a tool for a specific job (and yes I know mysql ab is a real company) in a new environment where existing technologies were just to cumbersome or overfeatured with the wrong features.
Compared to access on an nt box, mysql 2 was a godsend back in 2000. Of course now the idea of dynamic websites has really taken off and everyone is realising that the alternatives like ASP are not fit-for-function so php is the fastest growing web dev tool around.
We always have our favourites, but half of the skill of being an IT Pro is knowing how to choose the right tool for the job, not knowing how to make a tool or two do every job - that is just nerdism not professionalism.
Originally posted by Roger Ramjet
Yes. Like PHP, it does not conform to various 'standards',
Which "standards" does PHP not comply with? The only relevant one I can think of off-hand is the use of <? to begin a PHP block, conflicting with XML's use of same. Unfortunately, the designers of XML didn't know about PHP....
Failure to comply with internationally-ratified standards (such as the SQL definition) is a bug. Either a bug, or a Microsoft-style marketing strategy designed to lock in users.
If MySQL claims to be an SQL RDBMS (as the name suggests) then there's every reason to expect it to be compliant. How hard would that have been to implement? It's not like there's anything in the SQL definition that makes it inappropriate for "new technologies" like web development.
