April 24, 2014

Idea: Couple of more string types

MySQL has a lot of string data types – CHAR, VARCHAR, BLOB, TEXT, ENUM and bunch of variants such as VARBINARY but I think it is not enough I would also like to see type HEXCHAR which would be able to store hex strings, such as those returned as MD5() and SHA1() efficiently. With little […]

Using MMM to ALTER huge tables

Few months ago, I wrote about a faster way to do certain table modifications online. It works well when all you want is to remove auto_increment or change ENUM values. When it comes to changes that really require table to be rebuilt – adding/dropping columns or indexes, changing data type, converting data to different character […]

Hacking to make ALTER TABLE online for certain changes

Suppose you want to remove auto_increment from 100G table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE huge_table CHANGE id id int(6) NOT NULL and then wait hours for table rebuild to complete. If you’re unlucky i.e. you have a lot of indexes and not too much RAM – you could end […]

Redundant index is not always bad

About year ago Peter wrote about redundant indexes and mentioned sometimes it is good to leave two indexes, even one is first part of another. I’m speaking about BTREE indexes, for example, KEY (A), and KEY (A,B). From SQL point of view KEY(A) is not needed, as for queries like WHERE A=5 the index (A,B) […]