Bangin' on a Rok Amarok, KDE, and all that good stuff

DB changes — call for benchmarkers!

I've done some work in trunk over the past week that may have a huge impact on many of you Amarokers. Read on, and if you can do some benchmarks for me, fantastic.

First, the schema/table changes.

1. We've seen some issues where people have, for whatever reason, ended up with InnoDB tables instead of MyISAM tables. This is probably the result of their DB being created long ago before we were explicitly telling the mysqle startup to skip InnoDB. This mainly causes a problem because some columns cannot be as wide as we'd like them to be when using InnoDB. So, the first thing being done is that an ALTER TABLE is being forced on every table to explicitly convert to MyISAM. In addition, ENGINE parameters are now used during table creation to be more explicit in the future.
2. Some of you might have seen complaints in the debug output about indexes not being able to be created due to a max key length, which by default in MySQL is 1000 (compile-time option). So, some columns have had their widths adjusted so that all indexes are now successfully created.

Now, the other changes:

As we added more features, scanning got slow. Like, really slow. You'd spend more time running SQL queries than actually scanning your files. So I've been aiming to change that.

Over the past week I've committed changes that remove, per track, anywhere from 1 to 6 SQL queries. The exact amount is highly dependent on your file set, but there is a minimum of one less SQL query per track. If you've done a lot of file moves and AFT kicks in, it'll be an even more massive speedup. I'm going to try to do some further tuning, but already results are looking positive.

Nikolaj has reported that his scan time went from 68 seconds to 18 seconds -- more than 3x faster. Mikko didn't notice a speedup, but he said that whereas scanning used to peg his CPU at 100%, it no longer does so. What I want to know is: how does this affect *you*?

If you want to help, do the following:

1. Backup your DB. If you're using external MySQL do a mysqldump, if you're using internal MySQLe backup the mysqle folder in the Amarok data directory.
2. Update to a revision from a week ago...say, 995000.
3. Wipe your DB.
4. Start Amarok -- it will do a full scan because of the empty DB. Time it as it does the scan.
5. Repeat steps 3 & 4, so that you can see what the time is like after caching.
6. Update to current trunk (at least 998470).
7. Repeat step 3.
8. Repeat steps 4 and 5.

Then leave a reply here with your values. If you watch your CPU during each of the scans, report that here too. Thanks!