/** * Adds extra indices for reverse foreign keys * This is required for MySQL databases, * and is called from Database::doFinalInitialization() */ public function addExtraIndices() { /** * A collection of indexed columns. The keys is the column name * (concatenated with a comma in the case of multi-col index), the value is * an array with the names of the indexes that index these columns. We use * it to determine which additional indexes must be created for foreign * keys. It could also be used to detect duplicate indexes, but this is not * implemented yet. * @var array */ $_indices = array(); $this->collectIndexedColumns('PRIMARY', $this->getPrimaryKey(), $_indices); $_tableIndices = array_merge($this->getIndices(), $this->getUnices()); foreach ($_tableIndices as $_index) { $this->collectIndexedColumns($_index->getName(), $_index->getColumns(), $_indices); } // we're determining which tables have foreign keys that point to this table, // since MySQL needs an index on any column that is referenced by another table // (yep, MySQL _is_ a PITA) $counter = 0; foreach ($this->getReferrers() as $foreignKey) { $referencedColumns = $foreignKey->getForeignColumnObjects(); $referencedColumnsHash = $this->getColumnList($referencedColumns); if (!array_key_exists($referencedColumnsHash, $_indices)) { // no matching index defined in the schema, so we have to create one $index = new Index(); $index->setName(sprintf('I_referenced_%s_%s', $foreignKey->getName(), ++$counter)); $index->setColumns($referencedColumns); $index->resetColumnSize(); $this->addIndex($index); // Add this new index to our collection, otherwise we might add it again (bug #725) $this->collectIndexedColumns($index->getName(), $referencedColumns, $_indices); } } // we're adding indices for this table foreign keys foreach ($this->getForeignKeys() as $foreignKey) { $localColumns = $foreignKey->getLocalColumnObjects(); $localColumnsHash = $this->getColumnList($localColumns); if (!array_key_exists($localColumnsHash, $_indices)) { // no matching index defined in the schema, so we have to create one. MySQL needs indices on any columns that serve as foreign keys. these are not auto-created prior to 4.1.2 $index = new Index(); $index->setName(substr_replace($foreignKey->getName(), 'FI_', strrpos($foreignKey->getName(), 'FK_'), 3)); $index->setColumns($localColumns); $index->resetColumnSize(); $this->addIndex($index); $this->collectIndexedColumns($index->getName(), $localColumns, $_indices); } } }
/** * <p>Adds extra indices for multi-part primary key columns.</p> * * <p>For databases like MySQL, values in a where clause much * match key part order from the left to right. So, in the key * definition <code>PRIMARY KEY (FOO_ID, BAR_ID)</code>, * <code>FOO_ID</code> <i>must</i> be the first element used in * the <code>where</code> clause of the SQL query used against * this table for the primary key index to be used. This feature * could cause problems under MySQL with heavily indexed tables, * as MySQL currently only supports 16 indices per table (i.e. it * might cause too many indices to be created).</p> * * <p>See <a href="http://www.mysql.com/doc/E/X/EXPLAIN.html">the * manual</a> for a better description of why heavy indexing is * useful for quickly searchable database tables.</p> */ private function doHeavyIndexing() { if (self::DEBUG) { print "doHeavyIndex() called on table " . $this->name . "\n"; } $pk = $this->getPrimaryKey(); $size = count($pk); // We start at an offset of 1 because the entire column // list is generally implicitly indexed by the fact that // it's a primary key. for ($i = 1; $i < $size; $i++) { $idx = new Index(); $idx->setColumns(array_slice($pk, $i, $size)); $this->addIndex($idx); } }