Hi,
We have a busy website (Currently 200+ online)
Our search box is having terrible issues,
Some of the SQL searches are causing the SQL server to crawl to a stop and go offline,
Our hosting company has given is the queries that have been submitted, and think for a simple enough product search is entering huge searches, It appears huge amounts of data are being searched for no reason.
Has anyone had similar errors?
Below are 2 sample errors we have been told are hogging the system
By the way the SQL is a seperate server with 28gb RAM and 16 Core processor,
It appears the code is getting stuck in a loop somewhere or something, that causes the server to just reach tipping point and go so slow that the front end server sees the MYSQL as offline
The hosting company say the error stems from core/fn.catalog.php around line #3409
// Used for Extended search
if (!empty($params[‘get_conditions’])) {
return array($fields, $join, $condition);
}
if (!empty($params[‘limit’])) {
$limit = db_quote(" LIMIT 0, ?i", $params[‘limit’]);
}
$total = 0;
if (!empty($items_per_page)) {
if (!empty($params[‘limit’]) && $total > $params[‘limit’]) {
$total = $params[‘limit’];
}
$limit = fn_paginate($params[‘page’], 0, $items_per_page, true);
}
$products = db_get_array(‘SELECT SQL_CALC_FOUND_ROWS ’ . implode(’, ‘, $fields) . " FROM ?:products as products $join WHERE 1 $condition GROUP BY $group_by ORDER BY $sorting $limit");
if (!empty($items_per_page)) {
$total = db_get_found_rows();
fn_paginate($params[‘page’], $total, $items_per_page);
} else {
$total = count($products);
}
// Post processing
foreach ($products as $k => $v) {
$products[$k][‘category_ids’] = fn_convert_categories($v[‘category_ids’]);
}
if (!empty($params[‘item_ids’])) {
$products = fn_sort_by_ids($products, explode(’,', $params[‘item_ids’]));
}
if (!empty($params[‘pid’]) && !empty($params[‘apply_limit’]) && $params[‘apply_limit’]) {
$products = fn_sort_by_ids($products, $params[‘pid’]);
}
fn_set_hook(‘get_products_post’, $products);
return array($products, $params, $total);
}
Time: 100923 21:00:38
User@Host: XXXXXXe_cs2[XXXXXXXX_cs2] @ [172.16.0.2]
Query_time: 11 Lock_time: 0 Rows_sent: 30 Rows_examined: 14402
use XXXXXXXX_cs2;
SELECT SQL_CALC_FOUND_ROWS products.product_id, products.company_id, companies.company as company_name, descr1.product as product, products.tracking, products.feature_comparison, products.zero_price_action, products.product_type, products.tax_ids, products.weight, GROUP_CONCAT(IF(products_categories.link_type = ‘M’, CONCAT(products_categories.category_id, ‘M’), products_categories.category_id)) as category_ids, min_qty, max_qty, products.qty_step, products.list_qty_count, products.is_edp, avail_since, buy_in_advance, products.options_type, products.exceptions_type, companies.company as company_name, products.product_code, products.amount, MIN(prices.price) as price, products.status, products.list_price, descr1.short_description, IF(descr1.short_description = ‘’, descr1.full_description, ‘’) as full_description, cscart_seo_names.name as seo_name, IF(products.age_verification = ‘Y’, ‘Y’, IF(cscart_categories.age_verification = ‘Y’, ‘Y’, cscart_categories.parent_age_verification)) as age_verification, IF(products.age_limit > cscart_categories.age_limit, IF(products.age_limit > cscart_categories.parent_age_limit, products.age_limit, cscart_categories.parent_age_limit), IF(cscart_categories.age_limit > cscart_categories.parent_age_limit, cscart_categories.age_limit, cscart_categories.parent_age_limit)) as age_limit FROM cscart_products as products LEFT JOIN cscart_companies companies ON companies.company_id = products.company_id LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = ‘EN’ LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1 LEFT JOIN cscart_product_prices as prices_2 ON prices.product_id = prices_2.product_id AND prices_2.lower_limit = 1 AND prices_2.price < prices.price AND prices_2.usergroup_id IN (0, 0, 1) INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id AND (cscart_categories.usergroup_ids = ‘’ OR FIND_IN_SET(0, cscart_categories.usergroup_ids) OR FIND_IN_SET(1, cscart_categories.usergroup_ids)) AND (products.usergroup_ids = ‘’ OR FIND_IN_SET(0, products.usergroup_ids) OR FIND_IN_SET(1, products.usergroup_ids)) AND cscart_categories.status IN (‘A’, ‘H’) AND products.status IN (‘A’) LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = products.product_id AND cscart_seo_names.type = ‘p’ AND cscart_seo_names.dispatch = ‘’ AND cscart_seo_names.lang_code = ‘EN’ LEFT JOIN cscart_product_sales ON cscart_product_sales.product_id = products.product_id AND cscart_product_sales.category_id = products_categories.category_id WHERE 1 AND products.company_id = 0 AND (((descr1.search_words LIKE ‘%kids%’) OR descr1.short_description LIKE ‘%kids%’ OR (descr1.meta_keywords LIKE ‘%kids%’ OR descr1.meta_description LIKE ‘%kids%’))) AND products.status IN (‘A’) AND prices.usergroup_id IN (0, 0, 1) AND prices_2.price IS NULL GROUP BY products.product_id ORDER BY products_categories.position asc LIMIT 0, 30;
Time: 100923 21:00:40
User@Host: XXXXXX_cs2[XXXXXX_cs2] @ [172.16.0.2]
Query_time: 11 Lock_time: 0 Rows_sent: 2 Rows_examined: 13102
SELECT SQL_CALC_FOUND_ROWS products.product_id, products.company_id, companies.company as company_name, descr1.product as product, products.tracking, products.feature_comparison, products.zero_price_action, products.product_type, products.tax_ids, products.weight, GROUP_CONCAT(IF(products_categories.link_type = ‘M’, CONCAT(products_categories.category_id, ‘M’), products_categories.category_id)) as category_ids, min_qty, max_qty, products.qty_step, products.list_qty_count, products.is_edp, avail_since, buy_in_advance, products.options_type, products.exceptions_type, companies.company as company_name, products.product_code, products.amount, MIN(prices.price) as price, products.status, products.list_price, descr1.short_description, IF(descr1.short_description = ‘’, descr1.full_description, ‘’) as full_description, cscart_seo_names.name as seo_name, IF(products.age_verification = ‘Y’, ‘Y’, IF(cscart_categories.age_verification = ‘Y’, ‘Y’, cscart_categories.parent_age_verification)) as age_verification, IF(products.age_limit > cscart_categories.age_limit, IF(products.age_limit > cscart_categories.parent_age_limit, products.age_limit, cscart_categories.parent_age_limit), IF(cscart_categories.age_limit > cscart_categories.parent_age_limit, cscart_categories.age_limit, cscart_categories.parent_age_limit)) as age_limit FROM cscart_products as products LEFT JOIN cscart_companies companies ON companies.company_id = products.company_id LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = ‘EN’ LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1 LEFT JOIN cscart_product_prices as prices_2 ON prices.product_id = prices_2.product_id AND prices_2.lower_limit = 1 AND prices_2.price < prices.price AND prices_2.usergroup_id IN (0, 0, 1) INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id AND (cscart_categories.usergroup_ids = ‘’ OR FIND_IN_SET(0, cscart_categories.usergroup_ids) OR FIND_IN_SET(1, cscart_categories.usergroup_ids)) AND (products.usergroup_ids = ‘’ OR FIND_IN_SET(0, products.usergroup_ids) OR FIND_IN_SET(1, products.usergroup_ids)) AND cscart_categories.status IN (‘A’, ‘H’) AND products.status IN (‘A’) LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = products.product_id AND cscart_seo_names.type = ‘p’ AND cscart_seo_names.dispatch = ‘’ AND cscart_seo_names.lang_code = ‘EN’ LEFT JOIN cscart_product_sales ON cscart_product_sales.product_id = products.product_id AND cscart_product_sales.category_id = products_categories.category_id WHERE 1 AND products.company_id = 0 AND (((descr1.search_words LIKE ‘%amy%’) OR descr1.short_description LIKE ‘%amy%’ OR (descr1.meta_keywords LIKE ‘%amy%’ OR descr1.meta_description LIKE ‘%amy%’))) AND products.status IN (‘A’) AND prices.usergroup_id IN (0, 0, 1) AND prices_2.price IS NULL GROUP BY products.product_id ORDER BY products_categories.position asc LIMIT 0,
If you can help us to fix it we’ll pay you via paypal before the end of the week -
Thanks
Stephen