Generally, we set the data into a joint table, this helps us to show data without processing. Tables containing collective data can be named as flat tables and the managing procedures can be named as indexing. Here in this article, we come to know how we can speed up the processing and display without adjusting the standards of database normalization.
Anomalies of a non-normalized database
A modification anomaly is found when an attribute is present in multiple tables. In this scenario, the modification takes place at different locations. If not modified properly, it will lead the database to inconsistency.
We cannot insert a row when there is a missing piece of information. This results in loss of information and insertion anomaly occur.
If the required data is deleted this leads to a deletion anomaly.
Thus to avoid anomalies, one should organize the database according to the database’s normal forms.
The indexer speeds up the data display. The limitation of the indexer is that it does not contain basic data but processed data. Thus it fails in the first level of the normalization test. Hence it needs to match certain requirements.
The primary prerequisite can be met if just the indexing method deals with the table and we erase the table before each indexing.
We can’t delete such information, the flat table is not part of the framework, and it just displays them. The system should work without its support.
Illustration: We form the flat table; however we delete records meanwhile, which is not required. For this situation, the information in the flat table is not forward any longer. Now when we reindex, there will be data that is not required any longer. In this scenario, if we don’t delete the flat table, then we have to check if the given values are actually required or not. This would be a long and complex procedure. The best solution is to delete and rebuild the whole table. Later on, this action will be supportive.
Speed will be obtained automatically as the table contains only the most required data. Now all you need is to manage and update regularly.
Regular Solution:
The diagram above sates the following steps:
Advantage: Simple and logical.
Disadvantage: It is very slow. It cannot meet the requirements of flat tables. Practically this is not possible.
Ideal Solution:
Improved steps:
Advantages: It is very fast. Avoids the loss caused during communication between two systems. It also meets the criteria of the requirement.
Disadvantages: It is highly complicated. Thus it may happen, MySql may fail to create the processed data.
We can start the indexing process manually with cron. In this scenario reindexing is important as we are unaware of the records being up-to-date.
We cannot avoid reindexing completely as it only contains the required data which helps to manage the speed. On the other hand it is very costly. To minimize the cost one should only use this for certain rows only.
The database must be maintained in such a way that while inserting MySql it should automatically sate the modification if required.
One should be clear with two things:
It must be created in such a way that when inserted, MySQL must recognize whether there is a new or an existing record. Unique indexing is the best solution to it.
Provide a name to the table
<entities> <index_table> <table>custom_index_table</table> </index_table> </entities>
Now register the indexer
<global> ...... <index> <indexer> <some_key> <model>module/model</model> </some_key> </indexer> </index> ...... </global>
You can add unique indexes in the installer
$table->addIndex( $installer->getIdxName( 'your_namespace/your_table', array( 'column1', 'column2', 'column3', ), Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE ), array( 'column1', 'column2', 'column3', ), array('type' => Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE) );
By doing this you can get rid of the records generating in multiple locations in the table.
All we need is to go through three functions.
Runindex method:
Step 1:- Set the database adapter.
$resource = Mage::getSingleton('core/resource'); $adapter = $resource->getConnection('core_write');
Step 2:- Join the tables by requesting the model
$collection = Mage::getModel('namespace/model') ->getCollection() ->removeAllFieldsFromSelect() ->removeFieldFromSelect('id');
Step 3:- We have to remove all the columns of SELECT to adjust the index table. Then tables are joined for the data which will be required later on.
Example: ORDER ITEM join:
$collection->getSelect()->joinLeft( array('order_item' => Mage::getSingleton('core/resource')->getTableName('sales/order_item')), 'order_item.order_id = main_table.order_id', array() );
Column names and orders are defined with equivalent to a flat table.
$columns = array( 'column1', 'column2', 'column3', ); $collection->getSelect() ->columns('wishlist_item.product_id AS column1') ->columns('GROUP_CONCAT(customer_id SEPARATOR ",") AS column2') ->columns('SUM(wishlist_item.qty) AS column3');
Generate the request providing the flat table:
$select = $collection->getSelect();
Run and insert the table
$sql = $adapter->insertFromSelect($select, Mage::getSingleton('core/resource')->getTableName('namespace /custom_index_table'), $columns, Varien_Db_Adapter_Interface::INSERT_ON_DUPLICATE); $adapter->query($sql);
ReindexById method
Filter the records of SELECT:
$collection->getSelect()->where('id = '.$id);
ReindexAll
Now, delete the content in the index table and request the identifiers of all the records and call the
runReindex($id)
method.
<?php class Namespace_Model_Model extends Mage_Sales_Model_Order_Item { const ENTITY = 'namespace_model_model'; /** * Before Delete */ protected function _beforeDelete() { parent::_beforeDelete(); Mage::getSingleton('index/indexer')->logEvent( $this, self::ENTITY, Mage_Index_Model_Event::TYPE_DELETE ); } /** * Before Save */ protected function _beforeSave() { parent::_beforeSave(); Mage::getSingleton('index/indexer')->logEvent( $this, self::ENTITY, Mage_Index_Model_Event::TYPE_SAVE ); } /* * After Save Commit */ protected function _afterSaveCommit() { parent::_afterSaveCommit(); Mage::getSingleton('index/indexer')->indexEvents( self::ENTITY, Mage_Index_Model_Event::TYPE_SAVE ); } /* * After Delete Commit */ protected function _afterDeleteCommit() { parent::_afterDeleteCommit(); Mage::getSingleton('index/indexer')->indexEvents( self::ENTITY, Mage_Index_Model_Event::TYPE_DELETE ); } }
Only during modification and deletion, the data will be changed. Observation is needed for this event as Magento differentiates between index events. It depends on the programmer for which events should be monitored by the indexer.
When the indexer wants to observe an event not dispatched, it can be found in the Magento Core. Now the original class should be overwritten.
The indexer class monitors and runs the indexing processes. It must be created in the model directory of your module. Extension to this class must be from the Mage_Index_Model_Indexer_Abstract class.
class Namespace_Model_Indexer extends Mage_Index_Model_Indexer_Abstract
Observation of the event can be done through a class array.
/** * Index matched Entities array * * @var array */ protected $_matchedEntities = array( Namespace_Model_Model::ENTITY => array( Mage_Index_Model_Event::TYPE_SAVE, Mage_Index_Model_Event::TYPE_MASS_ACTION, Mage_Index_Model_Event::TYPE_DELETE ), );
Earlier we had declared the model’s events. We can see in the code above the function of the ENTITY constant value found in the class. Now we can identify the model. The following is the methods that need to be created:
/** * @return bool */ public function isVisible() { return true; } /** * Retrieve Indexer name * * @return string */ public function getName() { return Mage::helper('namespace')->__('Custom indexer'); } /** * Retrieve Indexer description * * @return string */ public function getDescription() { return Mage::helper('namespace')->__('Reorganize custom flat data'); } /** * Rebuild all index data */ public function reindexAll() { Mage::helper('namespace/indexer')->reindexAll(); }
Following is the method
/** * Register indexer required data inside event object * * @param Mage_Index_Model_Event $event */ protected function _registerEvent(Mage_Index_Model_Event $event) { $dataObj = $event->getDataObject(); if($event->getType() == Mage_Index_Model_Event::TYPE_SAVE){ $event->addNewData('id, $dataObj->getId()); }elseif($event->getType() == Mage_Index_Model_Event::TYPE_DELETE){ $event->addNewData('id, $dataObj->getId()); } }
Detect the event and add on the required data thereon for the indexing.
The ideal method is the _proccessEvent method.
/** * Process event based on event state data * * @param Mage_Index_Model_Event $event */ protected function _processEvent(Mage_Index_Model_Event $event) { $data = $event->getNewData(); if(!empty($data['id'])){ Mage::helper('namespace/indexer')->reindexById((int)$data['id']); } }
The target of any system is to achieve maximum speed. Flash Tables enable fast display and this is highly recommendable. The problem of trafficking in the database system must be tackled. Also, communication must be minimized.
Click one of our contacts below to chat on WhatsApp