I have a 8Go table in my CloudSQL database that (for now) doesn't have a primary key.
It is composed of 52 million rows of 20 columns each.
I would like to add one, since I will remove duplicates and doing so without primary key is too timeconsuming for MySQL
However, I am having issues to make this run, and I'm wondering if I'm actually doing it in the most efficient way.
I wanted to add this primary key using the following line :
ALTER TABLE mytable ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
I expected this request to increase the size of my database by a few hundreds MB, and run rather quickly.
However, when I ran the request, I saw the database usage increase a lot ( + 14 GB), and after 2 hours of running time, I saw the following error message :
"Error 1114 : the table 'mytable' is full".
When this error occurs, the database storage usage goes back to normal, but no change has been made to mytable
My questions are :
- What is CloudSQL trying to do when I launch my request, and how come it takes him so long ?
- What could I do to make my request executed more quickly by CloudSQL ? What am I missing ?
Thanks in advance,