After much pain I thought I should share my eventual success with amarok!
I have a 750gb external drive containing my mpfree collection, I also have a small local collection of 35gb on hda1: asking amarok to make a database of both always fails, this has proved true through the last number of releases using mysql. Track count is always different but invariably about 20,000 tracks short.
Also expanding various artists freezes the gui and takes about 5 mins, this has proved true on various powerful desktops 32 & 64bit, this also happened through the last number of releases.
Also if I fire up Amarok without first mounting sda1, the entire mysql database is wiped! It takes a number of hours to scan the tracks, so this is bad juju.
Solution:
turn off directory monitoring
turn off auto retrieve album art
NEVER open amarok without first mounting sda1
only have one source for your collection, sda1 & hda1 for some reason cannot be iterated together
turn off all gui elements that require http access, such as video/photo/wikipedia/lyrics etc
Only then does amarok become useable ... at least in my experience, after all the main reason to catalogue a collection is to search it.
I still cannot succesfully include both sda1 & hda1 in the database and I have given up on this issue.
hope this helps.

Could you maybe start by telling us which Amarok version you are talking about?

I have all my collection on an external HD which is permanently mounted, formatted as ext3, and never experienced any major problems with the collection since 2.2 (and not many more before that neither).

FWIW, the current Amarok version is 2.2.2

amarok: 2.2.2
kde: 4.3.4
mysql: 5.4.41-3

Debian

I just tried again, pushing some tracks from the external harddisk to the local one, added the local location to the collection and did a full collection rescan. It went without any problems, both locations are part of the database (I use the default embedded MySQL version). I really don't know what changes in your setup that makes it impossible to have both locations in the collection, it works for me.

I don't have that many tracks, roughly 10'000 but never experienced any freeze when expanding the Various Artists in the Collection Browser neither.

Using Amarok 2.2.3.-git on Kubuntu 9.10 with KDE SC 4.4 RC2, but I know it worked with 2.2.2 and KDE 4.3.4 before, too. MySQL version is the default embedded one, version 5.1.37-1ubuntu5

Did you try with a different user to exclude a settings problem?