[Wolves] Disable / enable keys slow in MySQL

[David Goodwin]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

DragonMaster wrote:
> I've posted a query on the mysql forum, but thought I would tap the fonts
> of all knowledge as well :-)
> 
> I've got a small database - about 14 million records and I run a weekly
> update on it which takes about 18 hours to run.  I've read that disabling
> the keys on the table, running the updates and then enabling the keys
> should be faster as the enable keys function uses a different algorithm.
> 
>   However, when I have done this, the rebuild takes 27 hours! - and that's
> without actually making any changes to the data!  Any ideas as to why
> this would be?
> 


It depends a lot on what you're doing to the data.

If you're doing a bulk insert (or perhaps update) then it would make
sense to disable the keys.

If your rebuild is using select statements then it won't... as removing
the indexes will mean it will take longer for mysql to find data

I'd suggest you try removing individual indexes - each one you remove
will make an update/insert/delete quicker - and a select slower :)

Also remember that if a table has an index on (a,b), you don't also need
an index on (a)... as MySQL can re-use the (a,b) index for (a) lookups.

David.

- --
David Goodwin

[ david at codepoets dot co dot uk ]
[ http://www.codepoets.co.uk       ]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkp5qD0ACgkQ/ISo3RF5V6aMmgCg6wBDQuOHmWeysJHDZwg1WCkb
iHcAnROmt+YIxBOBIFi9d1hUsU0gQufT
=k3e2
-----END PGP SIGNATURE-----