Very slow searh products on admin/store/orders/%uc_order/product_select
AexChecker - June 25, 2009 - 22:49
| Project: | Ubercart |
| Version: | 6.x-2.0-rc3 |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | needs work |
| Issue tags: | Scaling, ubercamp sprint |
Description
I researched code and fix it for correct and quickly search product.
<?php
// ....
function uc_order_load_product_select($order) {
$types = uc_product_types();
if (!empty($_POST['search'])) {
$search = check_plain($_POST['search']);
// use drupal_strtolower for correctly working with unicode
$search = drupal_strtolower(str_replace('*', '%', $search));
$search = '%'.trim($search, '%').'%';
$search_args = array_merge($types, array($search, $search));
$result = db_query("SELECT n.nid, n.title FROM {node} AS n LEFT JOIN "
."{uc_products} AS p ON n.nid = p.nid WHERE n.type IN "
."(". db_placeholders($types, 'varchar') .") AND (LOWER(n.title) LIKE '%s' OR LOWER(p.model) LIKE '%s')"
." ORDER BY n.title", $search_args);
}
else {
$result = db_query("SELECT nid, title FROM {node} WHERE type IN (". db_placeholders($types, 'varchar') .") "
."ORDER BY title", $types);
}
while ($row = db_fetch_object($result)) {
$options[$row->nid] = $row->title;
}
$output = drupal_get_form('uc_order_product_select_form', $order->order_id, $options);
print $output;
exit();
}
// ....
?>
#1
#2
i rolled these changes into a patch after reviewing the code path that leads up to this call. The only significant change (besides just prettification) that I saw was trimming '%'s from the edges of the search query, and then adding 2 on either end. Does this afford a significant performance gain? If so would someone mind explaining why? ;) I tried commenting as to why it appeared the change was made.
#3
Using drupal_strtolower() is a good idea, but I don't understand the trim(). It means that putting * on either end of your query doesn't really mean anything. I don't know how many people would expect that a * in the middle of the word will work.
And I don't see how it affects performance either, except that it might slow it down by adding two wildcards where there might not have been.
#4
A string
<?php$search = '%'.trim($search, '%').'%';
?>
do simpler work for employment (call-operator) when he or she typing product name or sku/model.
For example: if we have products with sku 1011, 1012, 1013 and 1041, 1042, 1043 and operator typing 101 then he/she get product listing with sku 1011, 1012, 1013.
I'm working now with some additional idea to simplify and quicken operator work:
1. Soundex
2. Misspalling
3. Remove vowel letters in sku/model/product_name string
The main goal of my work is to give out a maximum relevant entry of sku/model/product_name.
Because My work is support big e-stores with call-centers and many thousands products.
#5