What are the major differences between MySQL and SQLite?

Okay, from what i’ve picked up from wikipedia and what I know, MySQL is a seperate server process and can be run standalone on a server and requires more resources then the likes of SQLite. It runs like Oracle and MS SQL would, you connect to the database via sockets and all this. However SQLite runs integrated with the program, as wikipedia puts it:

“Unlike client-server database management systems, the SQLite engine is not a standalone process with which the program communicates. Instead, the SQLite library is linked in and thus becomes an integral part of the program.”

Therefore it entirely depends on what your needs are. If it’s really simple and not going to get too big data wise and you don’t need a huge amount of functionality then maybe SQLite is the way to go, it’s quicker then MySQL for smaller things and would be more then ample for distributed apps that only store data locally (Think Firefox, Skype, iPhones etc.) whereas MySQL is better as a server process if lots of data needs to be stored and it’s going to be used by lots of people. I’d only use SQLite if you’re really strapped for resources, MySQL doesn’t take up many resources on small scale stuff but if stuff grows many companies have multiple MySQL servers (see: http://en.wikipedia.org/wiki/Image:Wikimedia-servers-2006–05-09.svg, all those green ones are database servers, but we’re talking large scale here hehe) It entirely depends on what you’re using the database for, I hope this helps :)

MySQL can definitely be used for small things. You also have several different storage engine in MySQL to work with. SQLite is best used for very small amounts of data in running apps. It basically becomes a part of your application. PHP works well with MySQL, probably with SQLite as well though. Keep in mind that if your application grows, you might need more resources. That can be hard with SQLite.

SQLite locks the database (for read and write) during writes. So don’t use it for the web if you aren’t absolutely sure you’ll only ever have one person writing to the database at a time.

It is a boatload faster than MySQL, but that’s because there’s no server process and no network communication. Of course they’re completely different programs, but SQLite is more like a library that works on a file, whereas MySQL is a server multiple processes can connect to and issue SQL and get data back.

MySQL is good for apps of any scale, though I personally prefer PostgreSQL.