Magento: How to implement custom indexing within flash time?

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.

Quick guide for the contents in the article

• Anomalies of a non-normalized database.
• Requirements for Data Indexer.
• Event Oriented Indexing.
• Magento Indexing.

Anomalies of a non-normalized database

We mainly come across three types of anomalies, which can be listed as under.

1. Modification anomaly.
2. Insertion anomaly.
3. Deletion anomaly.

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.

What are the Requirements for Data Indexer?

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 following are the two main requirements

• The deletion of a flat table from the system should not cause any anomaly.
• The system should work without flat tables.

The primary prerequisite can be met if just the indexing method deals with the table and we erase the table before each indexing.

What happens in the event that we have erased such information that would be required?

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:

1. Indexing procedure.

The diagram above sates the following steps:

1. PHP asks for the records to be indexed, from tables, and through various models.
2. MySQL request for data.
3. PHP creates the records with the help of cycles, usually with multi cycles.
4. PHP sends back the records one by one.
5. MySQL upgrades them.

Advantage: Simple and logical.

Disadvantage: It is very slow. It cannot meet the requirements of flat tables. Practically this is not possible.

Ideal Solution:

2. Procedure of indexing

Improved steps:

1. With the models, PHP creates the SQL SELECT which comes back in the flat table
2. MySQL executes this SELECT and forms the table in the database

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.

What is Event Oriented Indexing?

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:

1. At what point index list be run?

• If there is a change in the source table

2. Which are the records to be indexed?

• It must be run where the records of the data are located.

 

What is Magento Indexing?

Creation of Flat table

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.

Creation of indexing process.

All we need is to go through three functions.

• runReindex($id) – private
• reindexAll() – public
• reindexById($id) – public

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.

Dispatch the Event.

<?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.

Implementing the Magento Indexer

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();

}

What is Event recognition and management?

 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.

How to run 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']);

}

}

Summary:

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.