Fork me on GitHub
Current releases are 2.1.3 (9 Sept 2014) and 2.0.18.13 (5 Aug 2014)

Ready for 2.1? Find out if your plugins are compatible. 2.0 will no longer be updated after Dec 2014.

Issue when creating multi-column Indexes using Vanilla's Database Class

businessdadbusinessdad Stealth contributor MVP
edited March 2012 in Feedback

I was working with Vanilla's Database Class to create some tables for my plugin, and I noticed two issues in the mechanism that builds DDL statements.

1- It doesn't allow to create multi-column Indexes. I followed the code that applies schema changes (namely Gdn_MySQLStructure->_Create()) and I noticed there's no way to specify an index name and to include more than columns into it. By declaring a column index, a new index containing only that column will be created on the database.

Example

Gdn::Structure()  
->Table('MyTable')  
->PrimaryKey('MyTableID')  
->Column('SomeUniqueField', 'int', FALSE, 'unique')  
->Column('FirstName', 'varchar(100)', FALSE, 'index')  
->Column('LastName', 'varchar(100)', FALSE, 'index')  
->Set();

The resulting table will have, besides the Primary Key, the following indexes: - Ix_MyTable_SomeUniqueField -> Unique Index
- Ix_MyTable_FirstName -> Index
- Ix_MyTable_LastName -> Index

There's no way to create a single index containing FirstName and LastName.

2- It forces composite indexes for Unique Keys It may seem a contradiction of point 1, but it's not. It's not possible to explicitly create a multi-column index, but Vanilla creates one single Unique index, no matter how many columns have been indicated as unique.

Example

Gdn::Structure()  
->Table('MyTable')  
->PrimaryKey('MyTableID')  
->Column('SomeUniqueField', 'int', FALSE, 'unique')  
->Column('FirstName', 'varchar(100)', FALSE, 'unique')  
->Column('LastName', 'varchar(100)', FALSE, 'unique')  
->Set();

This time, the resulting table will have one single unique key, containing SomeUniqueField, FirstName and LastName. Again, it's not possible to explicitly create a multi-column index containing only the desired columns.

The above are serious limitations in the Schema functions, as designing Indexes properly is critical for optimal performances in a database. This is especially true for multi-column indexes, where the position of a column in the Index definition can make a significant difference.

Currently, as a workaround, I create the affected indexes using a manually written SQL Statement. This implies having to manually deal with schema changes between versions, which is not optimal. I could also try to modify the Core class and submit it to the Community for review, but I'd first have to find how the process to follow.

Comments

Sign In or Register to comment.