I have a table which has approx 15 million rows - I won't add the schema here but it has 96 columns (far too many in my opinion).
The current table collation is
latin1_swedish_ci but for full emoji support I need to change this using the following command:
ALTER TABLE posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Running this query when I view the processlist does the copying to a temp table - due to the size of the table takes ages I don't know exactly how long but it must be between 6-9 hours. I have to do this the same query on another table which is even bigger than this one so I'm worried how long this will take so I need to find a better approach for this?
When I run
show index from posts; I get 37 rows!?? Which explains why this is very slow.
What would be the best way to make this a much quicker process?