The modifications done to impact the performance of ALTER TABLE very often have an impact on all other types of queries due to the internal workings of the database management system (coming back to the buffer pool, the bigger it is, the more data it can cache.) The impact is almost always positive – just make sure to not overload your server when playing around with the settings.Different database management systems have certain limitations as to where the modifications done towards the ALTER TABLE query apply to: in many cases, database management systems only have one or two storage engines that support modifications relevant to this query (in MySQL, that’s InnoDB and Percona XtraDB, for other database management systems the results may differ.).for some, optimizing the parameters won’t do as much good as for others.) Different database management systems put a different “weight” on this query in terms of performance (i.e.Setting the innodb-buffer-pool-size parameter to an optimal size will be a good starting point, however, there are also a couple of things that you need to keep in mind as well: Setting the buffer pool size to 60-80% of the RAM available within your system is a good idea – for those who wonder, the buffer pool size and related settings in other database management systems refer to the amount of operating memory that can be used for mission-critical queries that modify data – such queries include ALTER TABLE as well (you should probably also look at the settings below the buffer pool size for the sake of your database, but that’s a topic for another blog.) To optimize the performance of ALTER TABLE, optimize the setting that deals with the inner working of data within your database instance: in MySQL, that’s innodb-buffer-pool-size. In SQL Server, the file is called ConfigurationFile.ini.In PostgreSQL and related database management systems (TimescaleDB and the like), the file is called nf.In MySQL, this file is my.cnf and can be found in a variety of locations, most likely within the /var/lib/mysql folder.Everything depends on your database configuration – all database management systems make use of parameters defined within a file that they’re dependent upon when completing such operations: In most cases, this process will take miliseconds and you won’t even notice it as you go along – yet, in some cases, this process can also take hours or even weeks to complete. Your RDBMS will switch the table A with the table B.Your RDBMS will perform all modifying operations within the table B.Your RDBMS will insert all of the data within the table A into the table B.Your RDBMS will create a table B that is exactly the same as the table A.Your RDBMS will take a copy of the data within the table A.The way ALTER TABLE works is a little different to SELECT, INSERT, UPDATE, or DELETE queries that you are so used to – once the statement is used, your database management system will go through a couple of phases (for convenience, the original table that you run queries on will be called A, and the other will be called B): Rename columns, add constraints, and do a whole bunch of other things.īy now, you should get it – the ALTER TABLE statement can perform pretty much any action related to modifying data within a table.change the VARCHAR data type to INT on a certain column, etc.) Change the row format of rows in a table.Specify options within a table (one can specify the size of AUTO_INCREMENT, specify the average row length, the default character set, default collations, set comments, set directories that hold indexes or data, etc.).Add, drop, discard, import, truncate, reorganize, repair, remove, or otherwise modify partitions.Add, modify, or delete indexes from a table.Add, modify, or drop columns belonging to a certain table.The ALTER TABLE statement can be used to: Rather simple, right? This simple statement has a lot of use cases – care to go through them with us? The Use Cases of ALTER TABLE
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |