Custom queries I: Comparison between SQLite and MySQL

I've already said in earlier posts that you can use SQLite or MySQL as a database for your website. Recently when I was working on one I had to move to MySQL (I'll tell you why later in this article). So I thought I'll tell you guys how to transition.
But first, let's compare.

SQLite:

• It is a lightweight DBMS
• It is mainly used for testing and for small websites that do not generate too much data.
• The biggest advantage is that it handles all the data through a .db file which can be transported elsewhere and used in the same way
• It supports up to 2^64 rows (wow!)
• On the downside, for the kind of data that giants like Facebook and Google generate, SQLite falls laughably short
• It also has low concurrency control, which means that it crashes if too many people are trying to access the same data

The last point, the concurrency control, was the main reason I decided to switch to MySQL. The database of the website was crashing again and again. Let's look at MySQL now.

MySQL:

• It is tough, inflexible and an extremely powerful DBMS
• It is used by Facebook, Google, Twitter, pretty much all the web giants that exist today
• It has very high concurrency control
• It has the potential to store and handle an unlimited amount of data
• It has quick crash recovery
• There is very low chance of deadlocks
• All in all, if you plan to build a large site this is the DBMS for you
• The only downside is that it does not generate any file as earlier, so there has to be one native machine running the DBMS

This article is just the first of three of the series Custom queries. The reason I'm writing these three is that the query API that Django provides is good, but it does not allow custom queries (as far as my knowledge goes). For example, you want to customize search parameters and find all people that satisfy multiple conditions. To do this, Python's database API is very useful and can be used very well in Django.

In the next article, we'll see how to write custom queries for SQLite.

Cheers!