Sql-server – Are there any issues with sparsely populated partitioned tables

Answering your questions above:

1. In RANGE COLUMNS (datetime) key word COLUMNS is unnecessary. It is used to specify a list of columns, where as you have only 1 column. The difference exists: for RANGE (datetime_column) you can specify partition boundaries in datetime format, whereas for RANGE TO_DAYS(datetime_column) partition boundaries should be effectively in date format.

2. Partitioning on datetime is OK and it is very often.

Please refer to MySQL Manual, Chapter 18. Partitioning.

If the only reason for you using partitioning is structuring data, then may I suggest you looking at MERGE Storage Engine. It allows to create a table consisting of other tables. When queries to this overarching table have data only in some tables, then only those tables are used. It is easier to set up and maintain, than partitioning.

Partitioning usually rather decreases ALTER TABLE speed. Index and data is stored in separate files for each partition (a separate .ibd file for INNODB if innodb_file_per_table=1), so that you might make alter in parallel. But partitioning add an extra processing step to choose the partition for storing data/index, thus the whole operation is made slower.

mysql> create table alter_test (id int primary key auto_increment, v   char(20));
mysql> load data infile '/tmp/1' into table alter_test;
Query OK, 500000 rows affected (9.98 sec)

mysql> alter table alter_test add index i_test(v);
Query OK, 0 rows affected (4.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table alter_test (id int primary key auto_increment, v  char(20)) partition by key(id) partitions 10;
mysql> load data infile '/tmp/1' into table alter_test;
Query OK, 500000 rows affected (9.98 sec)

mysql> alter table alter_test add index i_test(v);
Query OK, 0 rows affected (4.83 sec)

As for the shared lock for ALTER TABLE, all partitions are locked in the same time, so partitioning does not help.

Hints:

• Upgrade to MySQL 5.6, since it supports online alter table.
• Use scripts such as oak-online-alter-table or pt-online-schema-change as a workaround for MySQL version < 5.6