faster with MyISAM tables than with InnoDB or NDB tables
http://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations.html
Performance considerations. Some effects of partitioning operations on performance are given in the following list:
-
File system operations. Partitioning and repartitioning operations (such as
ALTER TABLE
withPARTITION BY ...
,REORGANIZE PARTITIONS
, orREMOVE PARTITIONING
) depend on file system operations for their implementation. This means that the speed of these operations is affected by such factors as file system type and characteristics, disk speed, swap space, file handling efficiency of the operating system, and MySQL server options and variables that relate to file handling. In particular, you should make sure thatlarge_files_support
is enabled and thatopen_files_limit
is set properly. For partitioned tables using theMyISAM
storage engine, increasingmyisam_max_sort_file_size
may improve performance; partitioning and repartitioning operations involvingInnoDB
tables may be made more efficient by enablinginnodb_file_per_table
.See also Maximum number of partitions.
-
MyISAM and partition file descriptor usage. For a partitioned
MyISAM
table, MySQL uses 2 file descriptors for each partition, for each such table that is open. This means that you need many more file descriptors to perform operations on a partitionedMyISAM
table than on a table which is identical to it except that the latter table is not partitioned, particularly when performingALTER TABLE
operations.Assume a
MyISAM
tablet
with 100 partitions, such as the table created by this SQL statement:CREATE TABLE t (c1 VARCHAR(50)) PARTITION BY KEY (c1) PARTITIONS 100 ENGINE=MYISAM;
NoteFor brevity, we use
KEY
partitioning for the table shown in this example, but file descriptor usage as described here applies to all partitionedMyISAM
tables, regardless of the type of partitioning that is employed. Partitioned tables using other storage engines such asInnoDB
are not affected by this issue.Now assume that you wish to repartition
t
so that it has 101 partitions, using the statement shown here:ALTER TABLE t PARTITION BY KEY (c1) PARTITIONS 101;
To process this
ALTER TABLE
statement, MySQL uses 402 file descriptors—that is, two for each of the 100 original partitions, plus two for each of the 101 new partitions. This is because all partitions (old and new) must be opened concurrently during the reorganization of the table data. It is recommended that, if you expect to perform such operations, you should make sure that--open-files-limit
is not set too low to accommodate them. -
Table locks. The process executing a partitioning operation on a table takes a write lock on the table. Reads from such tables are relatively unaffected; pending
INSERT
andUPDATE
operations are performed as soon as the partitioning operation has completed. -
Storage engine. Partitioning operations, queries, and update operations generally tend to be faster with
MyISAM
tables than withInnoDB
orNDB
tables. -
Indexes; partition pruning. As with nonpartitioned tables, proper use of indexes can speed up queries on partitioned tables significantly. In addition, designing partitioned tables and queries on these tables to take advantage of partition pruning can improve performance dramatically. SeeSection 21.4, “Partition Pruning”, for more information.
Previously, index condition pushdown was not supported for partitioned tables. This limitation was removed in MySQL 5.7.3. See Section 9.2.1.6, “Index Condition Pushdown Optimization”.
-
Performance with LOAD DATA. In MySQL 5.7,
LOAD DATA
uses buffering to improve performance. You should be aware that the buffer uses 130 KB memory per partition to achieve this.