SQLite and the importance of indexes

I didn’t spend too much time on this issue, but as someone who knows the basics to intermediate in SQL it still caught me off-guard on how easy it is to have insanely slow query performances.

Take an innocent query like this. Once would think it has some sort of meta information stored already, but it doesn’t

select mytable.*, 
   (select count(*) 
    from mytable2 
    where mytable2.id=mytable.id ) as count
 from some_table;

What it does is that it will get all the columns in mytable and additionally it performs a COUNT based on id in a subquery thus the result is the total count per ID.

It took 7662ms to perform the query on two tables that have 387, and 4,800+ rows respectively. That’s not acceptable in any way, putting that sort of query in production will make your customers leave eventually due to the slowness of the system.

I googled a bit on the issue and found that sqlite doesn’t store any meta or index. I went ahead and create an index based on the second table, as the first one already has an index (primary key).

I managed to trim those seconds to 240ms, which imho is pretty acceptable, yet makes me feel uneasy.

The result?

select mytable.*, 
   (select count(ROWID)
    from mytable2 
    indexed by my_awesome_index
    where mytable2.id=mytable.id ) as count
 from some_table;

That’s all it took.

I’m still not completely satisfied with results like this, I will continue finding a way to trim those 200ms away to 20 or 50. I have an idea on how to do it but for now this will do.

Pelican2Ghost plug-in released

Pelican2Ghost is a plugin for Pelican site static generator that generates a JSON file so you can import it in Ghost blogging platform.

How to use?
git clone git@github.com:allenskd/pelican2ghost.git

Open your pelicanconf.py and add

PLUGIN_PATHS = ['/my/base/path']
PLUGINS = ['pelican2ghost']

For more information please check pelican2ghost Github

I found a bug!

Please do not comment that you found a bug. I’d appreciate it if you could report it, here.