{"id":1293,"date":"2012-08-14T15:07:00","date_gmt":"2012-08-14T14:07:00","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=1293"},"modified":"2019-04-08T11:06:22","modified_gmt":"2019-04-08T10:06:22","slug":"alter-optimization","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2012\/08\/14\/alter-optimization\/","title":{"rendered":"ALTER Optimization"},"content":{"rendered":"<p>One of my colleague asked me\u00a0: \u00ab Is an ALTER TABLE with many specifications is faster than 1 ALTER TABLE by specification ?\u00a0\u00bb<\/p>\n<p>The answers seems quite intuitive, anyway figures are better than a long speech&#8230;<\/p>\n<p>&nbsp;<\/p>\n<p>Context\u00a0:<\/p>\n<ul>\n<li>MySQL Community Server 5.5.22<\/li>\n<li>InnoDB table with 8 539 238 rows.<\/li>\n<li>17 columns with INT, CHAR, VARCHAR, TEXT, DATETIME, \u2026 fields<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Adding 3 indexes<\/span> (with a single command)<\/p>\n<p>&nbsp;<\/p>\n<pre>\r\nmysql5.5&gt; ALTER TABLE lien\r\n\r\n-&gt; ADD KEY `IdxIdMembre` (`IdMembre`),\r\n\r\n-&gt; ADD KEY `IdxCreationTitre` (`Creation`,`Titre`(100)),\r\n\r\n-&gt; ADD KEY `IdxPaysLangueCreation` (`Pays`,`Langue`,`Creation`);\r\n\r\nQuery OK, 0 rows affected (<strong>2 min 26.43 sec<\/strong>)\r\n\r\nRecords: 0 Duplicates: 0 Warnings: 0\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>Total: <span style=\"color: #ff0000;\"><strong>146.43<\/strong><\/span> sec<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Deleting the index<\/span> (with a single command)<\/p>\n<p>&nbsp;<\/p>\n<pre>\r\nmysql5.5&gt; ALTER TABLE lien\r\n\r\n-&gt; DROP KEY `IdxIdMembre`,\r\n\r\n-&gt; DROP KEY `IdxCreationTitre`,\r\n\r\n-&gt; DROP KEY `IdxPaysLangueCreation`;\r\n\r\nQuery OK, 0 rows affected (<span style=\"color: #355e00;\"><strong>1.78 sec<\/strong><\/span>)\r\n\r\nRecords: 0 Duplicates: 0 Warnings: 0\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>Note: With MySQL 5.5, delete secondary indexes (not PK) is costless.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Adding 3 indexes<\/span> (with 3 commands)<\/p>\n<p>&nbsp;<\/p>\n<pre>\r\nmysql5.5&gt; ALTER TABLE lien ADD KEY `IdxIdMembre` (`IdMembre`);\r\n\r\nQuery OK, 0 rows affected (<strong>26.34 sec<\/strong>)\r\n\r\nRecords: 0 Duplicates: 0 Warnings: 0\r\n<\/pre>\n<p>&nbsp;<\/p>\n<pre>\r\nmysql5.5&gt; ALTER TABLE lien ADD KEY `IdxCreationTitre` (`Creation`,`Titre`(100));\r\n\r\nQuery OK, 0 rows affected (<strong>57.58 sec<\/strong>)\r\n\r\nRecords: 0 Duplicates: 0 Warnings: 0\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>mysql5.5&gt; ALTER TABLE lien ADD KEY `IdxPaysLangueCreation` (`Pays`,`Langue`,`Creation`);<\/p>\n<p>Query OK, 0 rows affected (<strong>1 min 7.24 sec<\/strong>)<\/p>\n<p>Records: 0 Duplicates: 0 Warnings: 0<\/p>\n<p>&nbsp;<\/p>\n<p>Total: <span style=\"color: #ff0000;\"><strong>151.16<\/strong><\/span> sec (<strong>4\u00a0% slower<\/strong>)<\/p>\n<p>&nbsp;<\/p>\n<p>Of course, more data to moved implies a wider gap between the 2 methods<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">Deleting the index<\/span> (with 3 commands)<\/p>\n<p>&nbsp;<\/p>\n<pre>\r\nmysql5.5&gt; ALTER TABLE lien Drop index `IdxIdMembre`;\r\n\r\nQuery OK, 0 rows affected (<strong>0.29 sec<\/strong>)\r\n\r\nRecords: 0 Duplicates: 0 Warnings: 0\r\n<\/pre>\n<p>&nbsp;<\/p>\n<pre>\r\nmysql5.5&gt; ALTER TABLE lien DROP KEY `IdxCreationTitre`;\r\n\r\nQuery OK, 0 rows affected (<strong>1.06 sec<\/strong>)\r\n\r\nRecords: 0 Duplicates: 0 Warnings: 0\r\n<\/pre>\n<p>&nbsp;<\/p>\n<pre>\r\nmysql5.5&gt; ALTER TABLE lien DROP KEY `IdxPaysLangueCreation`;\r\n\r\nQuery OK, 0 rows affected (<strong>0.42 sec<\/strong>)\r\n\r\nRecords: 0 Duplicates: 0 Warnings: 0\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>Total: <span style=\"color: #355e00;\"><strong>1.77 sec<\/strong><\/span><\/p>\n<p>&nbsp;<\/p>\n<p>QED<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h3>Divers<\/h3>\n<p>&nbsp;<\/p>\n<pre>\r\nCREATE TABLE `lien` (\r\n\r\n`id` int(11) unsigned NOT NULL AUTO_INCREMENT,\r\n\r\n`Url` varchar(330) COLLATE utf8_swedish_ci NOT NULL,\r\n\r\n`Titre` text COLLATE utf8_swedish_ci NOT NULL,\r\n\r\n`IdMembre` int(11) NOT NULL,\r\n\r\n`Langue` char(2) COLLATE utf8_swedish_ci NOT NULL DEFAULT 'fr',\r\n\r\n`Pays` char(2) COLLATE utf8_swedish_ci DEFAULT NULL,\r\n\r\n`Encoding` varchar(32) COLLATE utf8_swedish_ci DEFAULT NULL,\r\n\r\n`Affiliate` int(11) DEFAULT NULL,\r\n\r\n`PictureUrl` varchar(512) COLLATE utf8_swedish_ci DEFAULT NULL,\r\n\r\n`Overview` text COLLATE utf8_swedish_ci,\r\n\r\n`hasIllustration` tinyint(1) DEFAULT '0',\r\n\r\n`EndDate` date DEFAULT NULL,\r\n\r\n`Creation` datetime DEFAULT '2008-10-27 00:00:00',\r\n\r\n`Visible` tinyint(1) DEFAULT '0',\r\n\r\n`Keywords` text COLLATE utf8_swedish_ci,\r\n\r\n`Source` int(1) NOT NULL DEFAULT '0',\r\n\r\n`DomainId` int(11) unsigned NOT NULL,\r\n\r\nPRIMARY KEY (`id`)\r\n\r\n) ENGINE=InnoDB AUTO_INCREMENT=8901321 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<pre>\r\nshow table status like 'lien'\\G\r\n\r\n*************************** 1. row ***************************\r\n\r\nName: lien\r\n\r\nEngine: InnoDB\r\n\r\nVersion: 10\r\n\r\nRow_format: Compact\r\n\r\nRows: 8427138\r\n\r\nAvg_row_length: 609\r\n\r\nData_length: 5139070976\r\n\r\nMax_data_length: 0\r\n\r\nIndex_length: 0\r\n\r\nData_free: 4194304\r\n\r\nAuto_increment: 8901321\r\n\r\nCreate_time: 2012-05-29 14:56:48\r\n\r\nUpdate_time: NULL\r\n\r\nCheck_time: NULL\r\n\r\nCollation: utf8_swedish_ci\r\n\r\nChecksum: NULL\r\n\r\nCreate_options:\r\n\r\nComment:\r\n<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>One of my colleague asked me : \u00ab Is an ALTER TABLE with many specifications is faster than 1 ALTER TABLE by specification ? \u00bb<\/p>\n<p>The answers seems quite intuitive, anyway figures are better than a long speech&#8230;<\/p>\n<p>Context :<\/p>\n<p>    MySQL Community Server 5.5.22<\/p>\n<p>    InnoDB table with 8 539 238 rows.<\/p>\n<p>    17 columns with INT, CHAR, VARCHAR, TEXT, DATETIME, \u2026 fields<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[203],"tags":[190,191],"class_list":["post-1293","post","type-post","status-publish","format-standard","hentry","category-mysql-en","tag-alter-table","tag-optimization"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-kR","jetpack-related-posts":[{"id":8116,"url":"https:\/\/dasini.net\/blog\/2025\/08\/19\/archive-smarter-query-faster-unlocking-mysql-performance-with-heatwave\/","url_meta":{"origin":1293,"position":0},"title":"Archive Smarter, Query Faster: Unlocking MySQL Performance with HeatWave","author":"Olivier DASINI","date":"19 ao\u00fbt 2025","format":false,"excerpt":"As databases grow, DBAs and developers often face a tough choice: + Keep historical data in production tables and risk slow queries + Archive it and lose fast access With HeatWave, you don\u2019t have to choose. You can archive old partitions, keep recent data in production, and still run lightning-fast\u2026","rel":"","context":"Dans &quot;Cloud&quot;","block_context":{"text":"Cloud","link":"https:\/\/dasini.net\/blog\/category\/cloud-en\/"},"img":{"alt_text":"HeatWave","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/12\/HeatWave_logo.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/12\/HeatWave_logo.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/12\/HeatWave_logo.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/12\/HeatWave_logo.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":2746,"url":"https:\/\/dasini.net\/blog\/2019\/03\/14\/mysql-functional-indexes\/","url_meta":{"origin":1293,"position":1},"title":"MySQL Functional Indexes","author":"Olivier DASINI","date":"14 mars 2019","format":false,"excerpt":"Since MySQL 5.7 one can put indexes on expressions, aka functional indexes, using generated columns. Basically you first need to use the generated column to define the functional expression, then indexed this column. Quite useful when dealing with JSON functions, you can find an example here and the documentation there.\u2026","rel":"","context":"Dans &quot;Astuce&quot;","block_context":{"text":"Astuce","link":"https:\/\/dasini.net\/blog\/category\/astuce\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1515,"url":"https:\/\/dasini.net\/blog\/2015\/11\/17\/30-mins-with-json-in-mysql\/","url_meta":{"origin":1293,"position":2},"title":"30 mins with JSON in MySQL","author":"Olivier DASINI","date":"17 novembre 2015","format":false,"excerpt":"MySQL 5.7 is GA and has over than 150 new features. One of them is a Native JSON Data Type and JSON Functions: \"Allows for efficient and flexible storage, search and manipulation of schema-less data. Enhancements include a new internal binary format, support for easy integration within SQL, and index\u2026","rel":"","context":"Dans &quot;json&quot;","block_context":{"text":"json","link":"https:\/\/dasini.net\/blog\/category\/json\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2248,"url":"https:\/\/dasini.net\/blog\/2018\/04\/10\/mysql-security-mysql-enterprise-transparent-data-encryption\/","url_meta":{"origin":1293,"position":3},"title":"MySQL Security &#8211; MySQL Enterprise Transparent Data Encryption","author":"Olivier DASINI","date":"10 avril 2018","format":false,"excerpt":"In this sixth episode of the\u00a0MySQL Security series, we will see\u00a0how data-at-rest encryption\u00a0helps organizations implement stronger security controls and satisfy regulatory compliance. You will be able to protect the privacy of your information, prevent data breaches and help meet popular regulatory requirements including GDPR, PCI DSS, HIPAA with MySQL Enterprise\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"MySQL Security","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_DB_Lock2.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":3031,"url":"https:\/\/dasini.net\/blog\/2019\/05\/07\/constant-folding-optimization-in-mysql-8-0\/","url_meta":{"origin":1293,"position":4},"title":"Constant-Folding Optimization in MySQL 8.0","author":"Olivier DASINI","date":"7 mai 2019","format":false,"excerpt":"In MySQL 8.0.16 the optimizer has improved again! Comparisons of columns of numeric types with constant values are checked and folded or removed for invalid or out-of-rage values. The goal is to speed up query execution.","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":7822,"url":"https:\/\/dasini.net\/blog\/2025\/06\/26\/handle-csv-files-with-heatwave-mysql\/","url_meta":{"origin":1293,"position":5},"title":"Handle CSV files with HeatWave MySQL","author":"Olivier DASINI","date":"26 juin 2025","format":false,"excerpt":"Efficiently loading CSV data into your MySQL environment is a crucial step for many analytical workloads, especially when dealing with the substantial datasets common in today's data-driven world. Both MySQL Shell's parallel import utility and HeatWave's Auto Parallel Load feature offer robust and effective solutions, each catering to slightly different\u2026","rel":"","context":"Dans &quot;Cloud&quot;","block_context":{"text":"Cloud","link":"https:\/\/dasini.net\/blog\/category\/cloud-en\/"},"img":{"alt_text":"HeatWave","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/12\/HeatWave_logo.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/12\/HeatWave_logo.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/12\/HeatWave_logo.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/12\/HeatWave_logo.png?resize=700%2C400&ssl=1 2x"},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1293","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=1293"}],"version-history":[{"count":5,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1293\/revisions"}],"predecessor-version":[{"id":1298,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1293\/revisions\/1298"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=1293"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=1293"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=1293"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}