这是一个展示CREATE INDEX 和 DROP INDEX的速度和效率的语句序列。对于小的表, 使用的时间不超过1秒，取决于我们使用快或慢的技术, 所以我们查看“rows affected” 输出来验证操作可以避免表的重建。对于一个大表，效率的差异是显而易见的，因为跳过表的重建可以节省大量的时间。
\! clear \! echo "=== Create and drop index (small table, new/fast technique) ===" \! echo \! echo "Data size (kilobytes) before index created: " \! du -k data/test/small_table.ibd create index i_dtyp_small on small_table (data_type), algorithm=inplace; \! echo "Data size after index created: " \! du -k data/test/small_table.ibd drop index i_dtyp_small on small_table, algorithm=inplace; -- Compare against the older slower DDL. \! echo "=== Create and drop index (small table, old/slow technique) ===" \! echo \! echo "Data size (kilobytes) before index created: " \! du -k data/test/small_table.ibd create index i_dtyp_small on small_table (data_type), algorithm=copy; \! echo "Data size after index created: " \! du -k data/test/small_table.ibd drop index i_dtyp_small on small_table, algorithm=copy; -- In the above example, we examined the "rows affected" number, -- ideally looking for a zero figure. Let's try again with a larger -- sample size, where we'll see that the actual time taken can -- vary significantly. \! echo "=== Create and drop index (big table, new/fast technique) ===" \! echo \! echo "Data size (kilobytes) before index created: " \! du -k data/test/big_table.ibd create index i_dtyp_big on big_table (data_type), algorithm=inplace; \! echo "Data size after index created: " \! du -k data/test/big_table.ibd drop index i_dtyp_big on big_table, algorithm=inplace; \! echo "=== Create and drop index (big table, old/slow technique) ===" \! echo \! echo "Data size (kilobytes) before index created: " \! du -k data/test/big_table.ibd create index i_dtyp_big on big_table (data_type), algorithm=copy; \! echo "Data size after index created: " \! du -k data/test/big_table.ibd drop index i_dtyp_big on big_table, algorithm=copy;运行以上代码会有如下的输出，浓缩为简洁和最重要的用粗体显示:
Query OK, 0 rows affected (0.00 sec) === Create and drop index (small table, new/fast technique) === Data size (kilobytes) before index created: 384 data/test/small_table.ibd Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 Data size after index created: 432 data/test/small_table.ibd Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) === Create and drop index (small table, old/slow technique) === Data size (kilobytes) before index created: 432 data/test/small_table.ibd Query OK, 1678 rows affected (0.12 sec) Records: 1678 Duplicates: 0 Warnings: 0 Data size after index created: 448 data/test/small_table.ibd Query OK, 1678 rows affected (0.10 sec) Records: 1678 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) === Create and drop index (big table, new/fast technique) === Data size (kilobytes) before index created: 315392 data/test/big_table.ibd Query OK, 0 rows affected (33.32 sec) Records: 0 Duplicates: 0 Warnings: 0 Data size after index created: 335872 data/test/big_table.ibd Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) === Create and drop index (big table, old/slow technique) === Data size (kilobytes) before index created: 335872 data/test/big_table.ibd Query OK, 1718272 rows affected (1 min 5.01 sec) Records: 1718272 Duplicates: 0 Warnings: 0 Data size after index created: 348160 data/test/big_table.ibd Query OK, 1718272 rows affected (46.59 sec) Records: 1718272 Duplicates: 0 Warnings: 0