SQLite ANALYZE demo

analyze_demo.py

import sys
import os
import sqlite3
import json
import datetime

db = "tmp.sqlite"

def create_db():
    if os.path.isfile(db):
        os.remove(db)
    global sqlite_conn
    sqlite_conn = sqlite3.connect(db)

def close_db():
    sqlite_conn.execute("vacuum")
    sqlite_conn.commit()
    sqlite_conn.close()
    os.remove(db)

create_db()
sqlite_conn.execute("""CREATE TABLE "cashes" (
    "id" INTEGER PRIMARY KEY  NOT NULL,
    "date" DATE NOT NULL,
    "uid" INT(10) NOT NULL DEFAULT ('1'),
    "visible" TINYINT(4) NOT NULL DEFAULT ('1')
)""")
sqlite_conn.execute("""CREATE INDEX "XIF_CASHES_USR" on cashes (uid ASC);""")
sqlite_conn.execute("""CREATE INDEX "XIF_CASHES_DUV" on cashes (date DESC, uid ASC, visible ASC);""")
for i in range(0, 10000):
    sqlite_conn.execute("""insert into cashes(id, date, uid) values({0}, '{1}', 1)""".format(i, (datetime.date.today() + datetime.timedelta(365/12*i)).strftime("%Y-%m-%d")));
    #sqlite_conn.execute("""insert into cashes(id, date, uid) values({0}, '2011-01-01', {0})""".format(i, (datetime.date.today() + datetime.timedelta(365/12*i)).strftime("%Y-%m-%d")));
sqlite_conn.execute("analyze cashes")
for row in sqlite_conn.execute("""EXPLAIN QUERY PLAN SELECT
      c.id, c.uid, c.date
     FROM cashes c
     WHERE
      c.date BETWEEN '2013-04-01' AND '2013-06-01'
      AND c.uid = 1 AND c.visible = 1
     ORDER BY
      c.date"""):
    print row

close_db()