Re: Database table redesign for minimize data and increse performance
Posted by: Rick James
Date: November 26, 2015 09:48PM

Show us the actual SELECTs.

Will you be deleting "old" records? If so, follow the partitioning tips in http://mysql.rjweb.org/doc.php/partitionmaint .

> `id` int(50) NOT NULL AUTO_INCREMENT,

The table currently has 600M rows? That `id` will overflow at 2 billion, so you are headed for another problem. INT UNSIGNED would let you go to 4 billion. (The "(50)" is meaningless.) BIGINT (8 bytes instead of 4) would take more space, but is essentially unlimited.

`event`, `useragent`, and apparently `email`, and perhaps some other columns, should be "normalized". This will greatly shrink the space for those fields. Suggest SMALLINT UNSIGNED (with a range of 0..65535) and another table for spelling them out. Before deciding on SMALLINT, do
SELECT COUNT(DISTINCT event), COUNT(DISTINCT useragent), COUNT(DISTINCT email) FROM sendgrid_data
to see if a 2-byte SMALLINT is the best choice for the foreseeable future. (A 1-byte TINYINT UNSIGNED is likely to suffice for `event`.)

Some discussion of the normalization can be found in http://mysql.rjweb.org/doc.php/staging_table .

You mentioned "performance"; please elaborate.

If you need to discuss how to ALTER and normalize 90GB without taking the system down for hours or days, we can get into that, too. Please provide, OS, disk size, MySQL version.

Options: ReplyQuote


Subject
Written By
Posted
Re: Database table redesign for minimize data and increse performance
November 26, 2015 09:48PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.