How to more efficiently search through an acoustid database with over 30 million rows?
Question: I'm currently playing around with an open source music recognition project
called acoustid. I've imported a table with over 30 million rows(300gb of
data) but it takes A TON of time to simply SELECT these rows. Currently,
selecting 200,000 rows can take 30 seconds.

The project offers acoustid-index to index the rows by only looking up the
first 15 seconds of a fingerprint and storing this on the hdd... which is then
loaded into ram. <https://bitbucket.org/acoustid/acoustid-index/overview>

Only, I have no idea how to use this.The directions are confusing. It seems
this was created for PostgreSQL. I'm using MySQL and Python on the server I'm
working on. Can I still use this to index my db?

Any suggestions as to how I use this to index the rows in the database? Are
there any other ways I can make the search through this database more
efficient?


Answer: In MySQL you can use an index on a BLOB/TEXT by defining the length you want
to apply this on:

    
    
    CREATE INDEX idx_nn_1 ON sometable(accoustic(500));
    

This would index the first 500 bytes as your fingerprint (ie: Not 15 seconds).

To get to 15 seconds, you could use a MD5SUM, add that as an extra column and
then query for the MD5SUM of that 15 seconds. Alternative you could just use a
MD5SUM over the complete song.

