Mike, I raised this issue when you made your first announcement about sakila, but since you asked to repeat, here goes.

The sakila-data.sql takes about 5 minutes to load, because it does not use transactions.

Adding "set autocommit=0;" at the beginning and a "commit;" after every table dump will get the load time down to 15 seconds.

Changing the single inserts to multiple inserts will get the load time down to 8 seconds.

We would be giving MySQL a bad name if we ship a data file that takes that long (unnecessarily!) to load.

Now, there may be some concerns, because mysqldump is not capable of creating a reliable optimized dump of sakila with one command, as it is today, due to some bugs that have been already reported and hopefully will be fixed
soon. (and due to the film_text table, that is supposed to be filled by a trigger)

If you have a Unix environment, you can produce an optimized dump with this bash script:

#!/bin/bash echo "use sakila;" > sakila-data.mysql echo "set autocommit=0;" >> sakila-data.mysql

TABLES=`mysql sakila -N -e 'show full tables' |\ grep BASE | \ grep -v film_text | \ perl -lane 'print $F[0]'`

OPTIONS1='--extended-insert --no-autocommit --quick --set-charset --hex-blob' OPTIONS2='--disable-keys --skip-triggers --skip-add-locks --no-create-info' DUMPOPTIONS="$OPTIONS1 $OPTIONS2"

for T in $TABLES ; do echo $T ; mysqldump $DUMPOPTIONS sakila $T | \ perl -pe 's/\),\(/),\n(/g' >> sakila-data.mysql ; done

The resulting script is 2 MB (compared to 4.5 MB of the original script) and it loads in 8 seconds (against 5 minutes).

Ciao Giuseppe

Mike Hillyer wrote: