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?

  • If you would post text results from SHOW CREATE TABLE posts; we would have an idea the magnitude of your data and indexes. SHOW INDEX FROM posts; should reveal all indexes (including covering indexes) and the cardinality for each of the items listed. Nov 19, 2019 at 19:10
  • It must copy the table over and rebuild the indexes. Show us the CREATE TABLE for the bigger table; maybe we can suggest some indexes to get rid of before you run the ALTER.
    – Rick James
    Nov 20, 2019 at 19:38
  • @zabs show index from posts; REBUILDS all the indexes before it is finished. We still would like to see the TEXT results of the 37 rows you had returned to see how may indexes you have on the table named posts, what they are for and the cardinality of each index. Nov 27, 2019 at 16:24


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.