You are here

When should you use MySQL and SQLite

Since I've been recently working on some additional web development projects to get my skills back to top shape and further my skills altogether, I decided I'd talk today on a topic that I couldn't get a straight answer to on the internet. So for those of you who don't know, many websites in this day and age are "dynamic". This means that the data is generated via scripts (basically, little programs) that pull data usually from a database and return it as a page to the user. Most of these websites rely on a database such as MySQL and this is all well and good for most websites. But it really depends. Mainly because running a database also utilizes background resources and retrieving information may require CPU and RAM to get the data back to the user. So firstly, let's talk about MySQL and Sqlite. MySQL is a full fledge compliant database that is extremely popular on the internet. To run mysql with InnoDB, it takes about 512MB of RAM to safely allow it to perform "OK". SQLite is a flat file database which doesn't require the sources of MySQL and might be limited in its functions but it does have it's place. Putting them to a test, it was noticeable that MySQL took a few seconds to do transactions (2 seconds or so) to load a Drupal page with memcache enabled as opposed to a SQlite database which was able to load the page in about 1 second. Overall drupal performance for a small website with about 100 content items is dramatically faster with SQLite. Now with that being said, if you have a lot of records (over 1000), SQLite may perform slower especially on indexed tables and this is where you should use MySQL to get the performance break you need. So in the end, it really depends on how much data you're working with to gain performance breaks.