insomnia bytes

The screaming groupies disturb my concentration, so I will reluctantly take a few minutes to silence the general hysteria. The secret is no more: we have über-optimized queries in XMMS2!

All thanks go to nesciens, who dived in the optimization documentation of SQLite to fix some stupid bottlenecks of my query generator. The two most important elements are the use of

for case-insensitive comparison (instead of using

, which disabled indexing), and the creation of better indices on the Media table (including collated indices).

Let’s get some numbers out!

Quick note: in addition to a pair of [key, value] indices (one with BINARY collation and one with NOCASE), he added a pair of [id, key, value] indices. I couldn’t highlight any effect on performance of the latter two, but I left them in for those tests. (Hint: more tests and insight are welcome!)

I ran a series of benchmarks with different search patterns. The execution time has been averaged over 100 executions ran with the command

. I compared the performance of execution in two versions of XMMS2:

• default DrKosmos (git hash: f171d33ca13e1715d3b167e8fa958a724eb032ce)
• optimized DrKosmos (git hash: 94661d22a7437b48c9cb5d4b1cd6483350d0a9a4, nesciens-sqloptims in my tree)

The queries used were the following (all matched songs):

read the rest of this entry »