inportb

I had a bit of time, so I decided to put SQLite+PHP5 to the test.

The task: keep track of some 12000 truetype font files.
The result: http://www.inportb.com/fontview/

Now, since SQLite is nothing but a flatfile database on steroids, I expected it to flake out when confronted with this much data. But I was very much surprised.

First, I ran all the font files through a PHP script that extracted various bits of metadata and stored everything in a SQLite database. This, understandably, took a while. I ended up with a compact database file less than 3MB, which I uploaded to my test server. All the server does is read the database. There is no writing, so the test is somewhat biased. But we already knew that SQLite isn’t very good when it comes to concurrent writes, so this test was designed to focus on reading.

I tried retrieving fonts by family and by the first letters in their names. Then I implemented a pagination mechanism. Everything ran smoothly enough.

But what I really wanted was a fuzzy similarity search. I wanted to use my own algorithm (in this case, it was the levenshtein method). Now, MySQL allows the definition of custom functions, but in most cases one is restricted from deploying them. SQLite stands out by allowing such functions to be defined in PHP. To see the fuzzy search in action, search for something like ‘zebra’. Now, there isn’t actually anything with that name, but it fetches you similar results. Much faster than I would have expected. Much faster than I could say “levenshtein”, even.

Okay, but what about putting new records into the database? Well, I don’t expect there to be much need for that yet, but it’s fast enough for the amount of writing that’s done now. Another test would have to be designed for that.

The conclusion: SQLite is an excellent DBMS for applications that primarily read data.

(Oh yeah, please feel free to take some fonts if you’d like.)