Take the 2-minute tour ×
Webmasters Stack Exchange is a question and answer site for pro webmasters. It's 100% free, no registration required.

Can sqlite3 be faster than MySQL on shared hosting and small to average websites (less than 500 visitors a day).

I have an account in a popular shared hosting provider and I've noticed that it has become quite slow redering pages. My doubt is that this may happen because the MySQL server is overloaded.

Some CMS'es work fine with SQLlite too, so I was wandering if I should use SQLite for the new sites instead of MySQL.

share|improve this question

1 Answer 1

up vote 9 down vote accepted

It's difficult to assess which one is going to be faster without additional details on how your shared hosting is configured.

An application that uses MySQL will need to make a connection to a MySQL server. This can be done to a remote/local host over TCP or to the local host via a Unix socket. The latter is likely to be slightly faster, since you won't have the overhead of TCP (even on localhost).

In contrast, SQLite uses a file on the same system directly. If it's on the same disk, it's likely to be generally faster than a connection to a separate server. However, some shared hosts used shared file-systems (e.g. NFS) where your actual file storage may be remote anyway.

Other factors are likely to have an impact:

  • The kind of locks and isolation required by your application.
  • The queries themselves and how the index are configured. I don't have an example in mind about SQLite v.s. MySQL, but PostgreSQL can create indices using functions (e.g. CREATE INDEX day_idx ON my_table(date_trunc('day', some_timestamp))), which MySQL can't.
  • Usage of prepared statements, possible caching, ...

All of this will vary depending on the actual application and specific configuration of your shared host. The only way to find out which one will work better is to try and compare.

share|improve this answer
1  
SQLite, as with most RDMBSs, has its own scaling considerations as well. Though if the site is small not just in visitors, but also in content, this shouldn't be an issue. But it should also be noted that SQLite doesn't have a client-server design like most RDBMSs. The SQLite client is doing all the work, so while a MySQL DB might be slower because of the network latency, the app could be faster overall due to the offloading of DB operations to the DB server. –  Lèse majesté Apr 2 '12 at 14:39
    
Do you know any testing tool? Some script that can be run on both MySQL and SQlite? –  Osvaldo Apr 2 '12 at 22:17
1  
There are a few questions on web performance testing on this site that list a few tools (e.g. this one). The main point here is that there's little point testing SQLite against MySQL if you don't test the application as a whole, preferably on the actual host (or something with similar characteristics). –  Bruno Apr 2 '12 at 23:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.