sqlite3 performance

I've been tweaking sqlite3 performance today.

Some things I've learned:

1. Sequelize is awesome, but it does not use bind variables.  Adding bind parameters to my SQL increased the speed a lot.  I'll use Sequelize for most of my stuff, but when I query a 1 million row table, I will just use node-sqlite3 because I can use bind parameters.
2. Indexes are huge for sqlite.  However, sqlite will only use one index per table in the SELECT query.  I have a view with maybe 4 tables, so luckily I can use one index per table.  Sqlite does not always choose the best index tho.
3. Using the sqlite3 command line with .timer on, .explain on, and explain query plan before your SQL statement are huge!  You want to be doing a search on an index, NOT a scan.

BTW, here's an example of the bind parameters.

var sql = "SELECT blah ";

sql += "FROM `mytable` ";

sql += "WHERE finishAt > ? and projectId=? and CustomerId = ? ORDER BY finishAt desc LIMIT 10000";

db = new sqlite3.Database('../mydb.sqlite', function() {

db.all(sql, ['2013-09-16',1,1], function(err, rows) {

if (err)

console.error(err);

console.log("Ran sql " + sql);

console.timeEnd("timestamp ");

// Force a single returned object into an array.

rows = [].concat(rows);

console.log("Got " + rows length + " records.");

if (rows.length > 0) {

console.log("Got the rows 0] " + JSON.stringify(rows[0]));

console.log("Got the rows ...");

}

db.close();

res.send(rows);

});

});