Indexes and Queries in SQLite: speed, Speed, SPEED!

June 26th, 2006
Elimite For Sale Cardizem No Prescription Buy Topamax No Prescription Buy Online Levitra Buy Lopid Online Zyban For Sale Trandate No Prescription Buy Viagra Soft Tabs No Prescription Buy Online Desyrel Buy Lariam Online Aldactone For Sale Cystone No Prescription Buy Levlen No Prescription Buy Online Colchicine Buy Epivir-hbv Online Detrol For Sale Myambutol No Prescription Buy Exelon No Prescription Buy Online Glucophage Buy Nolvadex Online Cardizem For Sale Augmentin No Prescription Buy Plan B No Prescription Buy Online Imitrex Buy Lisinopril Online

I have a small site about recreation on the Black Sea. Before knowing SQLite I afraid of MySQL (messing with a server is a serious examination to my nerves). I prefer file based databases and flat log files. I was quite happy with apache log files to find out hot pages and top ip addresses who visit my small site. So, I cron to parse my logs into SQLite database file.

$ time echo "select count(*) from crimeax;" | sql
real 0m0.117s

305866 rows in the database (parsed logs into SQLite DB file). All indexes are removed.

$ time echo "select count(distinct ip) from crimeax;" | sql
real 0m1.271s

$ time echo "select count(*) from (select ip from crimeax group by ip);" | sql
real 0m3.972s

First, get all ips from the table, then group them and count. 3.12 times slower. Lets index on ip to be created

sqlite> create index idx_ip on crimeax(ip);

About 20 seconds passed. Please note that inserting large chunk of data into the database with indexes may slow the process. So it’s advised to pour the data and THEN apply indexing.

$ time echo “select count(*) from (select ip from crimeax group by ip);” | sql
real 0m0.368s

Tenfold improve. And we can beat COUNT(DISTINCT IP) variant now by 3.4 times faster. Why not MySQL? Because it’s slower by 3X and not friendly with nested SELECTs.

June 26th, 2006

Considering a database for your project

June 25th, 2006

Explore the ways the databases may grow up

June 25th, 2006

Difference Between a SoftSwitch and a PBX

June 25th, 2006

Welcome to

June 25th, 2006