I have some advice for you fledgling webpeople out there.

Do not let your mySQL databases get too large. AudioMatch recently reached 10,000 users, of which more than half are very active.

Our database, as optimized as it is (it uses a lot of keys) was sitting at 1GB this morning. I thought it would be an easy task to delete all the older data entries and move them to an "archive" database.

Boy was I wrong. First of all, I left the "move IDs to new database" query running last night. It was still running when I woke up this morning. Ridiculous. It only transferred about 8 million ID points (~500 megs) ... so I decided to run with that.

Now, if you're familiar with SQL language, you'll think that it should be easy to delete all the old entries.

"DELETE FROM data WHERE songid < 8000000"

Heh. I can't delete that many at once without locking out mySQL queries from Tabulas and AudioMatch. I have to run them in short increments of 250,000 queries, and even that takes forever to run.

So the lesson here? Don't let your mySQL databases get too large. Once you hit 500megs, transfer the sucker over.

I'll be up most of tonight running my scripts in short increments ... trying to clear out the AM database. The problem is that as mySQL locks up trying to handle a 1gig database, it locks out all AM and Tabulas queries, which is crappy. Oh well. Live and learn :)


(But good news! We reached over 11,000,000 data entries for AM with 10,000+ users! Too bad we make no money...)

Edit: All done :) AM database is now down to a lively 600 megs. Yummy.