Sqlite vs MySQL popularity

kblessinggr

kblessinggr

PedoBeard
Sep 15, 2008
5,723
80
0
G.R., Michigan
www.kbeezie.com
This is not a debate between which is better, simply read the entire question and there won't be a problem :D

I'm thinking bout using Sqlite for some applications since PHP5 has native support for it (with PDO_sqlite etc).

The question is, how common is it for hosting with PHP5 to also have support for SQLite already included? Does this include most shared hosting providers?
 


Congrats on becoming enLitened, I am a huge proponent of SQLite and i can assure you PDO/SQlite is almost ubiquitous now days even on the most bare bones of hosting solutions It may be that you have to ask for it to be activated, buts its trivial and fast to do that.

There are a few "gotchas" for SQLite if you are coming from MySQL, but if you are not, you won't even notice the difference except maybe when you want to insert a bunch of things really fast.

In which case make sure to rap your statements in $pdo->beginTransaction(); $pdo->commit();

If you come from MySQL you may very well mis "ON DUPLICATE ..." but you are using php5 so you can wrap your inserts with a try{ } catch{}. You will NOT miss having to create a user account.

Many of you are using sqlite now, its what Firefox uses :D
 
Supergeek said:
Oh, that's why it's so slow to start up? :P I love Firefox, but it has its warts.
Click to expand...

FF is so slow because its meticulously planning how it can thrash your pager in the most gory and robust manor. I mean, how else they could come up with a way to use that much memory?

SQLite is equivalent in speed to MySQL on everything accept *inserts, the insert are slow because its very very careful about writing data to platter.

*Its not exactly the inserts, actually its more because the platter has to spin per transaction, thus wrapping your 50k inserts in one transaction makes them very fast again (~1 second).
 
Last edited:
Victory said:
Congrats on becoming enLitened, I am a huge proponent of SQLite and i can assure you PDO/SQlite is almost ubiquitous now days even on the most bare bones of hosting solutions It may be that you have to ask for it to be activated, buts its trivial and fast to do that.

There are a few "gotchas" for SQLite if you are coming from MySQL, but if you are not, you won't even notice the difference except maybe when you want to insert a bunch of things really fast.

In which case make sure to rap your statements in $pdo->beginTransaction(); $pdo->commit();

If you come from MySQL you may very well mis "ON DUPLICATE ..." but you are using php5 so you can wrap your inserts with a try{ } catch{}. You will NOT miss having to create a user account.
Click to expand...

I have read its best to create the SQlite/SQlite3 object as a PDO object instead of the Sqlite classes directly. The primary concern for the question is in regards to distribution to end users. By using a Sqlite db I think that would cut down on the need for the user to have to setup a mysql database and setup the appropiate user, but can also minimize the query access to a callback url accessing the local sqlite db file (and would be easier to backup).

The one thing I don't like is the insecure nature of it, meaning while its easy to write a file in an existing folder, finding (automatically) the one folder above the web-accessible folder may be a pain. Otherwise I think would have to somehow generate some kind of htaccess to prevent access to the file (perhaps prefixed with a period?)

I don't use On Duplicate and so forth, mainly a lot of select, delete, insert, update, the basic stuff. How easy do you think it would be to convert most of those to SQLite compliant queries from MySQL.

Many of you are using sqlite now, its what Firefox uses :D
Click to expand...

As I understand It's used quite a bit in desktop applications as well as mobile devices (hell even the ipod touch/iphone uses it for most of the apps)
 
kblessinggr said:
I have read its best to create the SQlite/SQlite3 object as a PDO object instead of the Sqlite classes directly.
Click to expand...

DO NOT use the sqlite_* classes, they are sqlite2 and you DO NOT want sqlite2. You want SQLite3.

The primary concern for the question is in regards to distribution to end users. By using a Sqlite db I think that would cut down on the need for the user to have to setup a mysql database and setup the appropiate user, but can also minimize the query access to a callback url accessing the local sqlite db file (and would be easier to backup).
Click to expand...
Backup is as easy as copying a file. Trivial.


The one thing I don't like is the insecure nature of it, meaning while its easy to write a file in an existing folder, finding (automatically) the one folder above the web-accessible folder may be a pain. Otherwise I think would have to somehow generate some kind of htaccess to prevent access to the file (perhaps prefixed with a period?)
Click to expand...
There are two ways to do this, one is to create a db/ folder. You can use __FILE__ to be reasonably sure about the path. IIRC I use this method for WhereYouBeen.

I defiantly use this method for my "SEO Empire" database sites.

the .htaccess is then

Code: 
# .htaccess in db/

<IfModule mod_rewrite.c>
RewriteEngine On
RewriteRule . - [F]
</IfModule>
This just forbids [F] looking at anything '.' in the dir. Note, '.' is much faster than '.*', i use to make this mistake, but '.*' actually requires matching the whole URL. I read about the optimization in "Definitive Guide to mod_rewrite", a nice, easy, fast book to read.

If for some reason you need to have your sqlite db in a folder which serves pages then you can just use ".sqlite$" as you forbid regex.

I don't use On Duplicate and so forth, mainly a lot of select, delete, insert, update, the basic stuff. How easy do you think it would be to convert most of those to SQLite compliant queries from MySQL.
Click to expand...
"The Basic Stuff" is almost always called CRUD; Create Retrive Update Delete.

Its hard to say how easy it will be to convert. It depends on how much your code relies on MySQL's default behavior.

For instance, if you insert a fifty letter string into a char(30) in MySQL it will be silently truncated, SQLite trusts you and will just store the whole darn thing.

Similarly if you have a "my_number int(10)" in SQLite and you try to "INSERT INTO table (my_number) VALUES ('i am nan')" it will still insert the string without bitching and moaning like MySQL. To have hard and fast type checking you need to use the "CHECK" declaration in your "CREATE TABLE" statements.


As I understand It's used quite a bit in desktop applications as well as mobile devices (hell even the ipod touch/iphone uses it for most of the apps)
Click to expand...
Yeah, its a super groovy db and all its code is in the Public Domain.

The documentation is very good.
 
Victory said:
DO NOT use the sqlite_* classes, they are sqlite2 and you DO NOT want sqlite2. You want SQLite3.
Click to expand...

Well there's also sqlite3_* classes but even then there's comments on php.net to use the pdo object. Guess main concern is , of all the PHP5 installations how common is version 3 installation.

Victory said:
Backup is as easy as copying a file. Trivial.
Click to expand...

Indeed.

Victory said:
There are two ways to do this, one is to create a db/ folder. You can use __FILE__ to be reasonably sure about the path. IIRC I use this method for WhereYouBeen.
Click to expand...

I typically just do something like $curpath = realpath('./');

Victory said:
I defiantly use this method for my "SEO Empire" database sites.

the .htaccess is then

Code: 
# .htaccess in db/

<IfModule mod_rewrite.c>
RewriteEngine On
RewriteRule . - [F]
</IfModule>
This just forbids [F] looking at anything '.' in the dir. Note, '.' is much faster than '.*', i use to make this mistake, but '.*' actually requires matching the whole URL. I read about the optimization in "Definitive Guide to mod_rewrite", a nice, easy, fast book to read.

If for some reason you need to have your sqlite db in a folder which serves pages then you can just use ".sqlite$" as you forbid regex.
Click to expand...

Most likely I'll have to look into the ability to include .htaccess files in a zip or tar.gz in regards to distribution. As would be somewhat nice to pre-secure it. However I guess I'll figure out some kind of pass-thru php script since the db file wouldn't be remotely accessible. (and on that note would probably need to allow user to configure a generic passkey to get a response for a query).

Victory said:
"The Basic Stuff" is almost always called CRUD; Create Retrive Update Delete.

Its hard to say how easy it will be to convert. It depends on how much your code relies on MySQL's default behavior.

For instance, if you insert a fifty letter string into a char(30) in MySQL it will be silently truncated, SQLite trusts you and will just store the whole darn thing.

Similarly if you have a "my_number int(10)" in SQLite and you try to "INSERT INTO table (my_number) VALUES ('i am nan')" it will still insert the string without bitching and moaning like MySQL. To have hard and fast type checking you need to use the "CHECK" declaration in your "CREATE TABLE" statements.
Click to expand...

Normally I don't leave things to chance, the main thing it looks like I'll need to change is change my method of creating 'id' fields, and to remember to explicitly assign NULL instead of omitting it from an insert query in order to force an auto_increment. Everything else I generally pull records by associative names, and when I insert I explicitly give it a list of fields instead of simply doing values in the assumed order. So from the look of it other than joins and so forth it seems like it may be easily converted.

Victory said:
Yeah, its a super groovy db and all its code is in the Public Domain.

The documentation is very good.
Click to expand...
 
Normally I don't leave things to chance, the main thing it looks like I'll need to change is change my method of creating 'id' fields, and to remember to explicitly assign NULL instead of omitting it from an insert query in order to force an auto_increment.
Click to expand...

All tables in sqlite3 automagically create ROWID, which is basically `id int primary key auto_increment" so there is no reason to create the `id` column that you would in MySQL or to pass the NULL paramater, its done automatically for you.
 
Your post has inspired me to blog about moving to PDO/SQLite from MySQL and to file a specific bug report on a problem you might encounter when doing prepared statements.

Basically "(product_id) VALUES ('1')" is not the same thing as "(product_id) VALUES (1)" because of how SQLite does type checking, but PDO::Execute() quotes integers with impunity.

Please cast your vote on how important you think this bug is on php.net
PHP Bugs: #49268: PDO::Execute and SQLite CHECK constarints quoting INTS (even if you think it doesn't matter, its helpful for them to know).
 
SQLite is significantly slower in my tests under php fast-cgi. It may have been because of the file lock but I dont think so.

And by significantly slower I mean for a script that does one update.

ab -c 100 -n 10000

Mysql would do 150ms average and sqlite would do like 500ms and had a much higher upper limit. I'll dig up the stats if you want but definitely benchmark your applications...

lol sorry I didn't read the question. I'm pretty sure sqlite is very common.
 
flysarescary said:
SQLite is significantly slower in my tests under php fast-cgi. It may have been because of the file lock but I dont think so.

And by significantly slower I mean for a script that does one update.

ab -c 100 -n 10000

Mysql would do 150ms average and sqlite would do like 500ms and had a much higher upper limit. I'll dig up the stats if you want but definitely benchmark your applications...

lol sorry I didn't read the question. I'm pretty sure sqlite is very common.
Click to expand...

Updates aside, how was the comparison between the two on simple select statements?
 
I don't have a benchmark on pure selects but my guess would be that mysql is still faster. I'm not a mysql guru but with mysql key buffer in memory it's pretty fast.

Here are the benchmarks for this script. Before it was essentially 1 update:

sqlite 2.8.17

Server Software: Cherokee/0.99.17
Server Port: 81

Document Length: 223 bytes

Concurrency Level: 100
Time taken for tests: 80.220 seconds
Complete requests: 10000
Failed requests: 1700
(Connect: 0, Receive: 0, Length: 1700, Exceptions: 0)
Write errors: 0
Non-2xx responses: 1700
Total transferred: 5643700 bytes
HTML transferred: 2247000 bytes
Requests per second: 124.66 [#/sec] (mean)
Time per request: 802.196 [ms] (mean)
Time per request: 8.022 [ms] (mean, across all concurrent requests)
Transfer rate: 68.70 [Kbytes/sec] received

Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 1.0 0 7
Processing: 5 799 772.9 602 4430
Waiting: 3 798 773.2 602 4425
Total: 5 799 773.5 602 4430

Percentage of the requests served within a certain time (ms)
50% 602
66% 644
75% 669
80% 683
90% 1693
95% 2870
98% 3637
99% 4398
100% 4430 (longest request)


sqlite 3.3.7

Server Software: Cherokee/0.99.17
Server Port: 81

Document Length: 304 bytes

Concurrency Level: 100
Time taken for tests: 98.782 seconds
Complete requests: 10000
Failed requests: 9800
(Connect: 0, Receive: 0, Length: 9800, Exceptions: 0)
Write errors: 0
Non-2xx responses: 1502
Total transferred: 6367858 bytes
HTML transferred: 2941656 bytes
Requests per second: 101.23 [#/sec] (mean)
Time per request: 987.818 [ms] (mean)
Time per request: 9.878 [ms] (mean, across all concurrent requests)
Transfer rate: 62.95 [Kbytes/sec] received

Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 1.1 0 19
Processing: 3 985 1234.6 760 9226
Waiting: 3 985 1234.9 759 9226
Total: 3 986 1235.2 760 9227

Percentage of the requests served within a certain time (ms)
50% 760
66% 833
75% 835
80% 1025
90% 1901
95% 3268
98% 5014
99% 7017
100% 9227 (longest request)



mysql 5.0.77

Server Software: Cherokee/0.99.17
Server Port: 81

Document Length: 223 bytes

Concurrency Level: 100
Time taken for tests: 54.006 seconds
Complete requests: 10000
Failed requests: 1600
(Connect: 0, Receive: 0, Length: 1600, Exceptions: 0)
Write errors: 0
Non-2xx responses: 1600
Total transferred: 5657600 bytes
HTML transferred: 2246000 bytes
Requests per second: 185.16 [#/sec] (mean)
Time per request: 540.060 [ms] (mean)
Time per request: 5.401 [ms] (mean, across all concurrent requests)
Transfer rate: 102.30 [Kbytes/sec] received

Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.9 0 7
Processing: 6 538 569.0 378 4223
Waiting: 3 537 569.4 378 4223
Total: 6 538 569.6 378 4228

Percentage of the requests served within a certain time (ms)
50% 378
66% 391
75% 393
80% 396
90% 1422
95% 1760
98% 2069
99% 3862
100% 4228 (longest request)


But then in my brilliance I switched it to just inserts:

This is ApacheBench, Version 2.3 <$Revision: 655654 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, Application Delivery Networking, Application Acceleration, Internet Traffic Management System : Zeus.com
mysql 5 unknown version

Completed 1000 requests
Completed 2000 requests
Completed 3000 requests
Completed 4000 requests
Completed 5000 requests
Completed 6000 requests
Completed 7000 requests
Completed 8000 requests
Completed 9000 requests
Completed 10000 requests
Finished 10000 requests


Server Software: Cherokee/0.99.17
Server Port: 80

Document Length: 38 bytes

Concurrency Level: 50
Time taken for tests: 9.487 seconds
Complete requests: 10000
Failed requests: 7737
(Connect: 0, Receive: 0, Length: 7737, Exceptions: 0)
Write errors: 0
Total transferred: 4919492 bytes
HTML transferred: 374746 bytes
Requests per second: 1054.02 [#/sec] (mean)
Time per request: 47.437 [ms] (mean)
Time per request: 0.949 [ms] (mean, across all concurrent requests)
Transfer rate: 506.37 [Kbytes/sec] received

Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.9 0 34
Processing: 11 47 13.2 46 108
Waiting: 3 15 6.1 14 49
Total: 11 47 13.2 47 109

Percentage of the requests served within a certain time (ms)
50% 47
66% 52
75% 56
80% 58
90% 65
95% 70
98% 76
99% 81
100% 109 (longest request)

inserts are super fast yay! sorry for not having anything of use about selects. check out and that blog in general:

http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

also, stop fucking coding and sling some berries mang!
 
Appropriate Uses For SQLite (tl;dr; fine for sites at or below 100k page views/day)

Right tool for the job is the mantra.

For the most part you should be thinking about how to optimize your select statements and when to use some page caching (http://www.danga.com/memcached/ or something like http://dfhu.org/blog/simple-php-cacheing i blogged about a couple weeks ago).

Don't use databases for that which could better taken care of by writting to a flat file and then processing all the information when needed (i.e. statistics).
 
Last edited:
seocracy said:
I keep thinking about looking into SQlite....it does seem lightweight, but I'm afraid of the downtime and the work required to switch over...I dont have any new projects coming up, but Im getting sick of dealing with bloated mysql tables and constantly rebuilding indexes.

......if only I had the time!
Click to expand...

I wouldn't switch any of your current sites over to SQLite unless there is a really good reason for it. I have tried to make libraries that work with either mysql or sqlite and its a real pain. Once you have picked a DB it becomes lot less likely that it makes sense to switch over for any given site.

If you are only doing one install of a site then mysql is normally fine, the benefits of sqlite, become less apparent.

For my networks, it basically goes if i am going to use Pylons or similar (i know your a RoR man) than use MySQL because the setup of the db is going to trivially small, and i got power tools with Python so it will be simple.

If i am putting up a bunch of sites or libraries which will be used on bunches of sites, then sqlite is the way to go.

tl;dr; PDO/Sqlite is the Bomb for replication, because hosting is cheap and straight forward, and you can rsync databases all over your network.
 
  • Like
Reactions: seocracy
Just wanted to toss this out there, Karl, if you intend to use it for KBLinker or some other commercial product I'm highly recommending against it. Here are the issues you're going to run in to:

HostGator. No support. Lots of your customers if you're not developing a hugely specialized product will be at HostGator, just ask your current ones.
GoDaddy. Last I heard, weird library-level permissions problems with SQLite.
BlueHost. No SQLite, iirc.
cPanel-latest (seems to be a configuration thing, YMMV and YcustomersMMV). Hates executing files in writable directories (and for good reason I guess), db file can't remain where your code is or you'll get a SoftPermissionsError.

Probably more stuff. I'd stick with MySQL for customer-software for now. For custom client work/your own stuff, thumbs up for Sqlite for sure. I love being able to back up my active databases with my code.
 
Whether or not it is, and frankly it isn't, MySQL is supported by nearly every host out there. By trying to break from the norm your really going to hurt your customer base, because most of them are retarded and have no idea how, or won't be allowed to, have Sqlite.

Stop thinking outside the box like you would be making things easier and start thinking about how you can make it more useful.

*my 2 cents*
 
Refrozen said:
Just wanted to toss this out there, Karl, if you intend to use it for KBLinker or some other commercial product I'm highly recommending against it. Here are the issues you're going to run in to:

HostGator. No support. Lots of your customers if you're not developing a hugely specialized product will be at HostGator, just ask your current ones.
GoDaddy. Last I heard, weird library-level permissions problems with SQLite.
BlueHost. No SQLite, iirc.
cPanel-latest (seems to be a configuration thing, YMMV and YcustomersMMV). Hates executing files in writable directories (and for good reason I guess), db file can't remain where your code is or you'll get a SoftPermissionsError.

Probably more stuff. I'd stick with MySQL for customer-software for now. For custom client work/your own stuff, thumbs up for Sqlite for sure. I love being able to back up my active databases with my code.
Click to expand...

If that were my intention I would do something like this:

PHP: 
$compat = false; //Not present, fall back to MySQL option
if(class_exists('PDO', $autoload))
    if(method_exists(PDO, 'getAvailableDrivers'))
        if(array_search("sqlite", PDO::getAvailableDrivers()) !== false)
            $compat = true; //SQLite3 Support Present

also there is PDO-SQLite3 support on a Godday free account:
http://fuck4chan.com/phpinfo.php

It seems most don't support SQLite3 via the classwrappers (like SQLiteConnection, etc), but I haven't run into one yet that didn't support it via PDO's driver (by default sqlite is 3 , and sqlite2 is 2).

Also apparently no write permission needs to be set with a free godaddy hosting, was able to run this without any problems.
PHP: 
if(is_writable('./'))
{
    touch('./config');
}
else
{
    if(file_exists('./config'))
    {
	if(!is_writable('./config'))
	    echo "Cannot write configuration";
	else
	    echo "can write configuration";
    }
    else { echo "file does not exist, and cannot be written"; }
}

I noticed PHP was running in fastcgi mode, and if they're using something like SuExec, then the php file is the same user as the owner instead of 'nobody', thus the files can remain 644 and folders 755 and still be writable and modified by php.
 
kblessinggr said:
If that were my intention I would do something like this:

PHP: 
$compat = false; //Not present, fall back to MySQL option
if(class_exists('PDO', $autoload))
    if(method_exists(PDO, 'getAvailableDrivers'))
        if(array_search("sqlite", PDO::getAvailableDrivers()) !== false)
            $compat = true; //SQLite3 Support Present
Click to expand...

I have done some of this and its a pain in the butt. You have to do a bunche of logic in PHP to meet each databases lowest common denominator. Also chances are that there is a "best" database for the job, either SQLite, MySQL, postgres or a flat file.

Its been my experience that Rage9's claims are a bit out of date. I know hostgator has PDO/Sqlite, you have to ask them to enable it, but it takes like 5 min.

Everything being said, if the script is only going to be run on one domain (per "user" i.e. you our your customer), then the time to set up mysql is more ore less trivial.
 
Rage9 said:
Whether or not it is, and frankly it isn't, MySQL is supported by nearly every host out there. By trying to break from the norm your really going to hurt your customer base, because most of them are retarded and have no idea how, or won't be allowed to, have Sqlite.

Stop thinking outside the box like you would be making things easier and start thinking about how you can make it more useful.

*my 2 cents*
Click to expand...



That's the thing bout SQLite, they wouldn't have to have an idea how. IF there is no support then they'd just use the usual MySQL setup by creating a database, user, filling out the info and so forth. But if they do have SQLite support, they don't have to do anything, as there's no user or file to be created by the user, and a backup/restore can be done via a single file.

Anywho original question was more rhetorical anyways.
 
You must log in or register to reply here.
Top Bottom