Restrictions and Limitations on Partitioning you should make sure that  large_files_support  is enabled and that  open_files_limit  is set properly. Partitioning and repartitioning operations involving  InnoDB  tables may be made more efficient by enabling  innodb_file_per_table . See also Maximum number of partitions. • Table locks.   Generally, 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  and  UPDATE  operations are performed as soon as the partitioning operation has completed. For  InnoDB -specific exceptions to this limitation, see Partitioning Operations. • 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. See Section 24.4, “Partition Pruning” , for more information. Index condition pushdown is supported for partitioned tables. See Section 8.2.1.6, “Index Condition Pushdown Optimization” . The maximum possible number of partitions for a given table not using the  NDB  storage engine is 8192. This number includes subpartitions. • Performance with LOAD DATA.   In MySQL 8.0,  LOAD DATA  uses buffering to improve performance. You should be aware that the buffer uses 130 KB memory per partition to achieve this. Maximum number of partitions.  The maximum possible number of user-defined partitions for a table using the  NDB  storage engine is determined according to the version of the NDB Cluster software being used, the number of data nodes, and other factors. See NDB and user-defined partitioning, for more information. If, when creating tables with a large number of partitions (but less than the maximum), you encounter an error message such as  Got error ... from storage engine: Out of resources when opening file , you may be able to address the issue by increasing the value of the open_files_limit  system variable. However, this is dependent on the operating system, and may not be possible or advisable on all platforms; see Section B.3.2.16, “File Not Found and Similar Errors” , for more information. In some cases, using large numbers (hundreds) of partitions may also not be advisable due to other concerns, so using more partitions does not automatically lead to better results. Foreign keys not supported for partitioned InnoDB tables.  Partitioned tables using the  InnoDB  storage engine do not support foreign keys. More specifically, this means that the following two statements are true: See also File system operations. 1. No definition of an  InnoDB  table employing user-defined partitioning may contain foreign key references; no  InnoDB  table whose definition contains foreign key references may be partitioned. 2. No  InnoDB  table definition may contain a foreign key reference to a user-partitioned table; no  InnoDB table with user-defined partitioning may contain columns referenced by foreign keys. The scope of the restrictions just listed includes all tables that use the  InnoDB  storage engine.  CREATE TABLE  and  ALTER TABLE  statements that would result in tables violating these restrictions are not allowed. ALTER TABLE ... ORDER BY.   An  ALTER TABLE ... ORDER BY  column  statement run against a partitioned table causes ordering of rows only within each partition. 4716