CODEWITHSUNDEEP
mysqlsqlperformancelongtext
maybethebestprogrammer
maybethebestprogrammer

Reputation: 13

Changing column from longtext to mediumtext taking over 1 hour

I am storing html in my database as text, and initially I used longtext because I just assumed it was what I needed. I queried my html and urls from my database using the full text and it took a long time and memory so I thought it was the large amount of html I was querying and decided to change longtext to mediumtext to make it faster, I have about 40,000 rows so I needed to change it before it gets higher. After running the query it's taking a long time and it's been over 1 and 1/2 hours. Is this usually how much time it takes or did I probably mess up somewhere?

Upvotes: 0

Views: 821

Answers (1)

Rick James
Rick James

Reputation: 142298

The ALTER must copy the entire table over and rebuild all(?) the indexes. That is what is taking "a long time".

Essentially the only differences between LONGTEXT and MEDIUMTEXT are

  • One extra byte per row for the 'length' for that column.
  • The limit for storing the data -- That is, a 17MB string will be chopped at 16KB when stored into MEDIUMTEXT.

Neither of those impact memory allocation or disk allocation (other than the 1 byte) or speed of anything. In both cases, your HTML is stored as a 3- or 4-byte length followed by as much or as little HTML as you give it.

If you are having performance problems, please provide a slow query and SHOW CREATE TABLE.

Upvotes: 0

Related Questions