If some of the values are 21 characters, dropping to 17 will lose data. You probably don't want to do that.
Be conservative -- what will future data be like? Maybe 22 characters? Drop the length to, say, 30.
No space will be saved by changing the max length -- in the "data" or in the "index". Well, not because of the change. However, most ALTERs ...
Have you thought about either using the first 8 characters or up until the first dash as a basis for partitioning? You should start by looking at how many unique values you would get when you partition by either the first 8 characters or the first dash. I don't want to assume that the first dash falls on the 9 character. Then you can try to partition with ...
... Oracle will be forced to read the full table in one query if the column isn't somehow optimized.
The word you're searching for is "indexed".
... this column is a primary key.
If this column is the Primary Key of the table, then it is indexed.
As such, queries for a individual values will be fast:
where id = ?
If your instances are accessible over the net than you can simply dump the prod instance on the dev instance via the host having required utilities and access to both instances:
mysqldump --host 10.20.30.40 \
-u myaccount1 \
| mysql --host 20.30.40....
Suggestions to consider for your my.cnf [mysqld] section
innodb_lru_scan_depth=100 # from 1024 to conserve 90% of CPU cycles used for function
innodb_buffer_pool_instances=8 # from 16 to reduce overhead until more RAM is avail
innodb_flush_neighbors=2 # from 0 to clear innodb_buffer_pool_pages_dirty quicker
thread_cache_size=100 # from 13 to reduce ...
Shane Bester made a suggestion as a comment on my bug report which explains that this is a known bug and will be resolved in next release. More importantly, there is a workaround which I've tried with great success. Thanks Shane wherever you are!
Shane said this:
Thanks for the test data. Found a workaround on 8....
There additional complexities in row format.
What has been discussed is InnoDB; MyISAM works differently.
InnoDB has 4 ROW_FORMATs. You should probably look at those if you would like some flexibility.
The general rule for InnoDB is to focus on where and how rows are stored. In this, the limit is about 8KB; more specifically a little less than half the ...
It is also stored the length of the data, that is why it is 9 - 12 , if you want it exactly.
/** The reference in a field for which data is stored on a different page.
The reference is at the end of the 'locally' stored part of the field.
'Locally' means storage in the ...
ibdata1 never shrinks.
.ibd files never shrink.
When a table is in ibdata1 and is ALTERed, space in that file is temporarily consumed, thereby possible stretching the file.
With .ibd, an ALTER will probably create a new copy, then toss the old one, thereby temporarily using diskspace, then releasing it.
There are too many variables to make any predictions. ...
Consider naming the partitions in an obvious way, such as
to contain data for the 19th (and defined LESS THAN ('2020-04-20'))
More tips: http://mysql.rjweb.org/doc.php/partitionmaint and its companion: http://mysql.rjweb.org/demo_part_maint.pl.txt
SHOW CREATE TABLE pmdemo....
Rate Per Second = RPS
Suggestions to consider for your AWS RDS Parameters group
key_cache_age_threshold=7200 # from 300 (seconds) to delay age out only to Read again
key_buffer_size=64M # from 16M to reduce key_reads RPS of ~ 50
key_cache_division_limit=50 # from 100 to have a Hot/Warm split in RAM
key_cache_block_size=16384 # from 1024 to reduce ...