Menu
Does anybody has experience of using partitioning feature in conjunction with the Doctrine2 library?
The first problem is that Doctrine creates foreign keys for association columns, anybody knows how to prevent or disable that?
And the second problem is how to specify custom table definition (PARTITION BY ...)?
Thanks in advance!
478
MySQL supports several types of partitioning as well as subpartitioning; see Section 22.2, “Partitioning Types”, and Section 22.2. 6, “Subpartitioning”. Section 22.3, “Partition Management”, covers methods of adding, removing, and altering partitions in existing partitioned tables.
We can create a partition in MySQL using the CREATE TABLE or ALTER TABLE statement. Below is the syntax of creating partition using CREATE TABLE command: CREATE TABLE [IF NOT EXISTS] table_name. (column_definitions)
There are a number of benefits that come with partitioning, but the two main advantages are: Increased performance - during scan operations, the MySQL optimizer knows what partitions contain the data that will satisfy a particular query and will access only those necessary partitions during query execution.
This is known as horizontal partitioning—that is, different rows of a table may be assigned to different physical partitions. MySQL 8.0 does not support vertical partitioning, in which different columns of a table are assigned to different physical partitions.
You're not out of luck!!
First, drop all foreign keys from all the tables D2 is managing. Copy & execute the result of this query:
SET SESSION group_concat_max_len=8192; -- // increase this if you do not see the full list of your tables
SELECT IFNULL(REPLACE(GROUP_CONCAT('ALTER TABLE ',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME,'; '), ',', ''), '') FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY';
Then override the supportsForeignKeyConstraints() method in /vendor/doctrine-dbal/lib/Doctrine/DBAL/Platforms/MySqlPlatform.php (or wherever this class is located) to:
public function supportsForeignKeyConstraints()
{
return false;
}
This will stop Doctrine from creating foreign key constraints on your next doctrine:schema:update command. After that you can simply execute an ALTER TABLE PARTITION BY... statement where needed (D2 doesn't support partitioning on a schema level). I recommend you backup & truncate your tables first (using --no-create-info) in order to have the structure changes executed as fast as possible and then restore them.
As this fellow says here, and based on my personal experience, D2 doesn't care whether you have FKs or not, as long as the proper relation definitions are in place.
P.S.: I'm currently working on extending the annotation syntax to support proper table & column definitions, including ENGINE (this might be useful), PARTITION BY & the @Column options array (i.e. {"fixed"=true, "unsigned"=true, "default"=0})
The overall effort amounts to a couple of sleepless nights for reverse-engineering & code patches, hope you do it faster :)
150
PARTITION engine in MySQL has major limitations with regard to keys. Please see latest docs, currently here: http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys-unique-keys.html
If Doctrine requires keys that Partition does not support, you are out of luck. Partition engine is very limited by design - it's intended for archival storage which is infrequently read. Few MySQL-aware apps will work with Partition, unless you make changes.
I would suggest using Partition as it was intended - archiving. Store your data in a more mainstream MySQL data engine would be the answer.
34
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
