New way to search through Magento administration grids – using *s
Why Magento canβt use indexes on columns in admin grid when you are searching for some value e.g. order#?
This article will be really short but extremely useful in case if you have a Magento website that have “several” administrators. I’ve worked on project that have more than 2.000 administrators with different roles (that number constantly grows). You probably can imagine how slow things can be when lots of administrators use administration, specially when they are searching or sorting some data (order# or first_name, etc.).
Is 2000 administrators big number for Magento? Depend how they use administration part, but lots of them wants to sort things, search through various columns for things that they donβt sometimes even need, e.g. sort by email address,…
For one test before we implement new functionality, try to search your sales_order grid in magento administration (if you have at least several orders) for order#: 10. Remember how many results youβve got for order#: 10 and how long your search lasts.
Why is searching through Magento grids so “expensive”?
Because MySQL server canβt use indexes when you are using LIKE statement for both side of some string that you are searching for. Remember that MySQL (innodb) has (mostly) B-Tree indexes implemented.
One example, you want find all customers that are using gmail as email address. You can search in email column, for customers in Magento admin grid, something like: “@gmail“. As result youβll find all gmail email addresses.
In that process, before you got result on screen, MySQL needs to go through all rows of customers and it needs to go through all chars for customer_email field.
For this specific case Magento is OK. But what will happen if you search for order#: 10001?
Same thing, because Magento always add “%” before and after your string for specific column. So if you want search for order#: 10001, Magento will convert that to “%10001%” and it will add LIKE statement in front of you string. So at the end Magento will send something like this to MySQL: “SELECT customer_email FROM…. WHERE customer_email LIKE ‘%10001%’;“. You probably didnβt expect that but thatβs how itβs implemented.
If we want to change that behavior and speed up MySQL Server (Magento) to use indexes weβll need to make some changes when we are searching in admin grid. Most common and user friendly way is to use character “*” as replacement for MySQL special char “%”.
New thing now will be that if you want to use “LIKE” operator you’ll need to add start in front of, or at the end of your string – or both. So after we make some changes, youβll be able to find all gmail email addresses by adding specifically “*” before and after you string. So “@gmail” will become “*@gmail*”. I hope you see idea behind it!
Let’s replace Magento’s method “protected function _addColumnFilterToCollection($column)” in app/code/core/Mage/Adminhtml/Block/Widget/Grid.php with following content and letβs affect new functionality only on sales_order grid (http://magento1510.loc/index.php/admin/sales_order/):
/**
*
* @author Inchoo <ivan .galambos@inchoo.net>
*/
protected function _addColumnFilterToCollection($column)
{
if ($this->getCollection()) {
$field = ( $column->getFilterIndex() ) ? $column->getFilterIndex() : $column->getIndex();
if ($column->getFilterConditionCallback()) {
call_user_func($column->getFilterConditionCallback(), $this->getCollection(), $column);
} else {
$cond = $column->getFilter()->getCondition();
/** START WITH CODE FOR A NEW WAY OF SEARCHING
* - if you look in app/code/core/Mage/Adminhtml/Block/Widget/Grid/Column/Filter/Abstract.php
* - you'll see that ->getCondition(); returns an array:
* public function getCondition()
* {
* return array('like'=>'%'.$this->_escapeValue($this->getValue()).'%');
* }
* - and _escapeValue returns:
* protected function _escapeValue($value)
* {
* return str_replace('_', '\_', $value);
* }
* - so we'll "unescape" it in case where we have [$cond['eq'] and unset($cond['like']);]
*/
if ($field && isset($cond)) {
if($this->getAction() instanceof Mage_Adminhtml_Sales_OrderController) {
if (isset($cond['like'])) {
//cover case where like should go to equal; else means that we have at least one * so like should stay...
if (!(substr($cond['like'], -2) === '*%') && !(substr($cond['like'], 0,2) === '%*')) {
$cond['eq'] = str_replace('%', '', $cond['like']);
$cond['eq'] = str_replace('\_', '_', $cond['eq']); //This line was added with new revision
unset($cond['like']);
} else {
if (substr($cond['like'], 0,2) !== '%*') {
$cond['like'] = substr($cond['like'],1);
} else {
$cond['like'] = '%' . substr($cond['like'],2);
}
if (substr($cond['like'], -2) !== '*%') {
$cond['like'] = substr($cond['like'], 0, -1);
} else {
$cond['like'] = substr($cond['like'], 0, -2) . '%';
}
}
}
}
/*END WITH CODE FOR A NEW WAY OF SEARCHING*/
$this->getCollection()->addFieldToFilter($field , $cond);
}
}
}
return $this;
}Now, after you’ve changed Magento core file try to search for order#: 10. How many result you see now? 0! How long that last? Instantly – Magento now use indexes for order#!
And all types that you can search now for number 10 are: “10”, “*10”, “*10*” and “10*”. Note that only first one is equality and other types use LIKE statement.
Note, you can remove this if block: “if($this->getAction() instanceof Mage_Adminhtml_Sales_OrderController) { … }” and apply changes to adminhtml module.
Of course you’ll not change Magento core file so for your homework rewrite Mage_Adminhtml_Block_Widget_Grid class.
This little and Neat trick had significantly improved performance on MySQL server for our client. Additional thing is to disable sorting for things that doesn’t help administrators (remember, administrators will click on sort often).
Why? Because they can π
P.S. In one of my next articles I’ll show you how you can notify all your admins, in similar way that Magento use for updates, etc. So stay tuned!
16 comments
How can we achieve this in Magento 2 since Magento 2 is using ui_component for its grid and while searching in ui grid, this method is not executing.
how about magento 2?
Thanks for the info and starting points for 1.9.2.2!
Sorry) Last comment was also wrong) Remove both and I provide workable solution tomorrow
After latest SUPEE-6788 we need to wrap all hardcoded expressions in Zend_Db_Expr.
In that case we need to replace:
With:
Even that will be better:
This variant is 100% workable for me:
Zhartaunik,
You have some misplaces single and double quotes, you also have an error trying to catch the ending *.
You have this.
if (substr($cond[‘like’], -3) !== “‘%*”) {
$cond[‘like’] = new Zend_Db_Expr(substr($cond[‘like’], 0, -2) . “‘”);
} else {
$cond[‘like’] = new Zend_Db_Expr(substr($cond[‘like’], 0, -3) . ‘%’);
}
Should be this.
if (substr($cond[‘like’], -3) !== “*%'”) {
$cond[‘like’] = new Zend_Db_Expr(substr($cond[‘like’], 0, -2) . “‘”);
} else {
$cond[‘like’] = new Zend_Db_Expr(substr($cond[‘like’], 0, -3) . “%'”);
}
So the complete working code with Mage 1.9.2.2 is
if ($field && isset($cond)) {
if($this->getAction() instanceof Mage_Adminhtml_Sales_OrderController) {
if (isset($cond[‘like’])) {
echo “This shit is… ” . substr($cond[‘like’], -3) . “” . substr($cond[‘like’], 0,3);
//cover case where like should go to equal; else means that we have at least one * so like should stay…
if (!(substr($cond[‘like’], -3) === “*%'”) && !(substr($cond[‘like’], 0,3) === “‘%*”)) {
$condition = str_replace(‘%’, ”, $cond[‘like’]);
$condition = str_replace(‘\_’, ‘_’, $condition);
$cond[‘eq’] = new Zend_Db_Expr($condition);
unset($cond[‘like’]);
} else {
if (substr($cond[‘like’], 0,3) !== “‘%*”) {
$cond[‘like’] = new Zend_Db_Expr(“‘” . substr($cond[‘like’],2));
} else {
$cond[‘like’] = new Zend_Db_Expr(“‘%” . substr($cond[‘like’],3));
}
if (substr($cond[‘like’], -3) !== “*%'”) {
$cond[‘like’] = new Zend_Db_Expr(substr($cond[‘like’], 0, -2) . “‘”);
} else {
$cond[‘like’] = new Zend_Db_Expr(substr($cond[‘like’], 0, -3) . “%'”);
}
}
}
}
Thanks Wolfgang and Tony π
@Wolfgang I did same thing for our 1.7.0.2. project but I didn’t have a time to update the post.
@everyone Please do some tests and see if on new version something has changed. Implementation will be very same/similar so don’t be afraid to add a note here.
Best Regards!
Thanks Ivan π
Hi,
I’ve changed your code in order to make it work with Magento CE 1.7.0.2.
Step 1: Cleaning up the search string (trim spaces and remove single quotes).
Step 2 (=> condition: eq): Remove all % if search string doesn’t contain any asterisk (*)
Step 3 (=> condition: like): Remove all % first, then replace all * by %. This way % can be used also WITHIN a string (i.e. *search*string*)
Step 4: removing if condition for sales_order_controller to make it work for all admin grids (product catalog, customers etc)
@cappuccino
“Youβve mentioned disabling sorting?” Because MySQL use, innodb, B-Tree indexes you need to create schema for sorting data on specific columns using multiple columns for indexes. Otherwise you can experience “file sorting” which is expensive operation in MySQL (you don’t use memory for sorting if you have lots of data that needs to be sorted – MySQL would copy data on file system and then it will sort everything. Additionally if you have GROUP BY statement then you’ll for sure experience “file sorting”).
So, MySQL use B-Tree indexes and you need to understand how it works to create useful indexes on your table to be able to sort data instantly!
For sure it works better. I get instantly results when I send some query from Magento administration grids. Instead of parsing whole table and each column for specific table + parsing each character in column’s field.
I agree with the idea of archive tables. I already did that on sales_flat_order_item table. Also be aware about partitioning in MySQL π
I’ll probably create some articles about best practices in MySQL with focus on performance gains.
Have a nice day!
Opps forgot to add, we’ve been thinking about a complementary speed booster to your solution for community edition magento (or non-order archiving users).
1) Adding a ‘archive’ column to the sales_flat_order_grid table + index
2) Add additional code to you code above to use the archive column to narrow down the search
From what I remember, MySQL should be able to use two indexes. Not sure if this would make things even faster… Thoughts?
Thanks Ivan, I can definitely see this helping when you start getting even a few dozen admins on a smaller server, using indexes as much as possible always a good thing.
You’ve mentioned disabling sorting? Would love a follow up article on this. Interesting to see what your experiences are, since, if the sorting is done on a indexed column, shouldn’t be a issue?
Along with this clever solution, have you found it works better / worse with particular mysql settings?
Your performance gain should also depend on the size of your store (db) and how many administrators are using administration.
Now we have more than 3500 administrators. Also with this implementation you should much faster get your results back from MySQL server because now you’re using indexes wherever they’re defined and where MySQL query optimizer can find solution for your query.
Before, instead of telling some admin order# as increment_id I always was telling them for order# as “url” order#. Now for me it’s same what I’ll provide them because we have indexes on required fields…
For joining… you can notice that Magento has 2 very similar tables. sales_flat_order and sales_flat_order_grid. sales_flat_order_grid table has only fewer columns but with same values as in sales_flat_order table. So there aren’t any joining and Magento had created for us that _grid table because of performance gains.
Hope this help!
interesting solution, though I didn’t see much of a performance gain, well not that I can notice.
how about removing all mysql table joining from the order page? is there any joining going on?