Menu Content

Support

> Forums, FAQs & Paid Support
Welcome, Guest
Username Password: Remember me

Very slow query searching in the customers table
(1 viewing) (1) Guest
Support forum for customers who have purchased this product. Archive only, no new post can be added.

NOTE: This category has been locked. If you have purchased paid version, please, use our Support Ticket system instead. If you are using free edition, please see the Community Support section.
  • Page:
  • 1

TOPIC: Very slow query searching in the customers table

Very slow query searching in the customers table 10 years, 7 months ago #40583

Hi

I have a problem with VMinvoices because the really very very slow query searching in the customers table when I'm creating a new order in the back office.

Could you help me to fix it please??

Thanks in advance!

Regards
The topic has been locked.

Re: Very slow query searching in the customers table 10 years, 7 months ago #40617

Any idea?
This is very urgent!! :S
The topic has been locked.

Re: Very slow query searching in the customers table 10 years, 7 months ago #40667

Hello,
I had the same problem in version 2.0.23, and changed the way this search as follows:

1) Tunning this querie in the file: \administrator\components\com_vminvoice\helpers\getter.php
In my case only consider in filter the fields after the WHERE clouse:
...
static function getAjaxUserList($filterOrig)
{
$db = JFactory::getDBO();
$filter = $db->Quote('%'.$db->getEscaped(JString::strtolower(JString::trim($filterOrig)), true).'%');

//get user list with all shipping adresses
if (COM_VMINVOICE_ISVM2)
{

//get user list with all shipping adresses
if (COM_VMINVOICE_ISVM2)
{

$searchId = is_numeric($filterOrig) ? ' OR BT.virtuemart_user_id = '.(int)$filterOrig : '';
$db->setQuery('SELECT ST.`address_type_name`, ST.`virtuemart_userinfo_id` AS st_user_info_id,
BT.`virtuemart_user_id` AS user_id, BT.`virtuemart_userinfo_id` AS bt_user_info_id,
BT.`last_name`, BT.`first_name`, BT.`title`, BT.`middle_name`, BT.`company`, BT.`city`
FROM `#__virtuemart_userinfos` AS BT
LEFT JOIN `#__virtuemart_userinfos` AS ST ON (BT.virtuemart_user_id = ST.virtuemart_user_id AND ST.`address_type` = "ST")
LEFT JOIN `#__users` AS U ON BT.virtuemart_user_id=U.id
WHERE (BT.`address_type` = "BT"
AND BT.`first_name` LIKE ' . $filter . '
AND BT.`first_name` LIKE ' . $filter . '
OR BT.`CUSTOM_FIELD_1` LIKE ' . $filter . '
'.$searchId . ' ) ORDER BY BT.`first_name`', 0, 50);

/* original querie commented below*/
/*
$searchId = is_numeric($filterOrig) ? ' OR BT.virtuemart_user_id = '.(int)$filterOrig : '';
$db->setQuery('SELECT ST.`address_type_name`, ST.`virtuemart_userinfo_id` AS st_user_info_id,
BT.`virtuemart_user_id` AS user_id, BT.`virtuemart_userinfo_id` AS bt_user_info_id,
BT.`last_name`, BT.`first_name`, BT.`title`, BT.`middle_name`, BT.`company`, BT.`city`
FROM `#__virtuemart_userinfos` AS BT
LEFT JOIN `#__virtuemart_userinfos` AS ST ON (BT.virtuemart_user_id = ST.virtuemart_user_id AND ST.`address_type` = "ST")
LEFT JOIN `#__users` AS U ON BT.virtuemart_user_id=U.id
WHERE (BT.`address_type` = "BT"
AND BT.`last_name` LIKE ' . $filter . '
'.$searchId.'
OR BT.`first_name` LIKE ' . $filter . '
OR U.`email` LIKE ' . $filter . '
OR BT.`company` LIKE ' . $filter . '
OR BT.`city` LIKE ' . $filter . '
OR ST.`address_type_name` LIKE ' . $filter . ' )
ORDER BY BT.`last_name`', 0, 50);
*/
}
else

...
2) Suggestion, change in the file "\administrator\components\com_vminvoice\views\order\tmpl\userinfo.php", on input field "user" the propertie "onkeyup" to execute the function "generateWhisper" only the "enter" is pressed.

[ ]`s
The topic has been locked.

Re: Very slow query searching in the customers table 10 years, 6 months ago #40987

Hi peterson.gomes

Thanks a lot for your help!

Solved! :)
The topic has been locked.
  • Page:
  • 1
User Login Empty