Re: Create index on parttion table (manually and dynamically)

[Rick James]

Adding an index to one partition is not possible.

ALTER TABLE REORGANIZE PARTITION ... will touch only the partition(s) indicated.

Adding/dropping of indexes is done for the entire table, not individual partitions.

MyISAM does table locks; InnoDB may have techniques that could solve your problem with less hassle. See pt-online-schema-change.

If you have 3 billion rows and you are low on disk space and you are in MyISAM, you are in serious trouble and you will have to take a very long outage.

If you would like to explain the _purpose_ of the task (not just how you plan to _implement_ the task), plus SHOW CREATE TABLE, SHOW TABLE STATUS, and the size of your current machine, we may be able to suggest alternative _solutions_.

It may involve migrating to another machine. It may involve an access layer that reaches into your current table, plus another table, to keep your application alive (no, or little, outage). It may involve something else "out of the box".