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

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
304866
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
28305
real 0m1.271s

$ time echo "select count(*) from (select ip from crimeax group by ip);" | sql
28305
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
28305
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.

2 Responses to “Indexes and Queries in SQLite: speed, Speed, SPEED!”

  1. red gucci handbags Says:

    thanks for your order and if you have hundreds of sites I would be definitley interested. let me know what you think when this gig is finished and we can definitley work out something

  2. chanson miracle Says:

    This blog site is really cool. How did you make it ?

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>