[Wolves] Disable / enable keys slow in MySQL

DragonMaster LUG at clews.homelinux.net
Wed Aug 5 15:56:05 UTC 2009


> 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.

The insert that I'm doing is actually very simple - it's an

INSERT...ON DUPLICATE KEY UPDATE...

There's just a lot of them :lol:

> 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

Ah... here comes the spanner... At this stage all I did was

ALTER TABLE tablename DISABLE KEYS;
ALTER TABLE tablename ENABLE KEYS;

This was the bit that takes 27 hours...

> 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.

I think I have a redundant index, so I'll try removing that - it has a
cardinality of 13000000...

Just to check, if I have an index on (a,b,c,d) and an index on (a,d) I
still need to keep the second index?

Many thanks,

DM






More information about the Wolves mailing list