Тормоза в админке на странице заказов. Около 30 000 заказов.

[halfhope 195]

Всем привет.

 

Тормозит страница заказов, а именно фильтрация. За фильтрацию отвечает функция GgetOrders($data = array()) модели sale/order.

public function getOrders($data = array()) {
	$sql = "SELECT o.order_id, CONCAT(o.firstname, ' ', o.lastname) AS customer, (SELECT os.name FROM " . DB_PREFIX . "order_status os WHERE os.order_status_id = o.order_status_id AND os.language_id = '" . (int)$this->config->get('config_language_id') . "') AS status, o.total, o.currency_code, o.currency_value, o.date_added, o.date_modified FROM `" . DB_PREFIX . "order` o";

	if (isset($data['filter_order_status_id']) && !is_null($data['filter_order_status_id'])) {
		$sql .= " WHERE o.order_status_id = '" . (int)$data['filter_order_status_id'] . "'";
	} else {
		$sql .= " WHERE o.order_status_id > '0'";
	}

	if (!empty($data['filter_order_id'])) {
		$sql .= " AND o.order_id = '" . (int)$data['filter_order_id'] . "'";
	}

	if (!empty($data['filter_customer'])) {
		$sql .= " AND CONCAT(o.firstname, ' ', o.lastname) LIKE '%" . $this->db->escape($data['filter_customer']) . "%'";
	}

	if (!empty($data['filter_date_added'])) {
		$sql .= " AND DATE(o.date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
	}
	
	if (!empty($data['filter_date_modified'])) {
		$sql .= " AND DATE(o.date_modified) = DATE('" . $this->db->escape($data['filter_date_modified']) . "')";
	}
	
	if (!empty($data['filter_total'])) {
		$sql .= " AND o.total = '" . (float)$data['filter_total'] . "'";
	}

	$sort_data = array(
		'o.order_id',
		'customer',
		'status',
		'o.date_added',
		'o.date_modified',
		'o.total'
	);

	if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
		$sql .= " ORDER BY " . $data['sort'];
	} else {
		$sql .= " ORDER BY o.order_id";
	}

	if (isset($data['order']) && ($data['order'] == 'DESC')) {
		$sql .= " DESC";
	} else {
		$sql .= " ASC";
	}

	if (isset($data['start']) || isset($data['limit'])) {
		if ($data['start'] < 0) {
			$data['start'] = 0;
		}

		if ($data['limit'] < 1) {
			$data['limit'] = 20;
		}

		$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
	}

	$query = $this->db->query($sql);

	return $query->rows;
} 

 

 

Прошу помощи у тех, кто уже встречался с таким, кто ее оптимизировал. Есть второй выход - удалить часть заказов, но это исключено. 

[freelancer 1 467]

не сталкивался, но если покажите expalin запроса, постараюсь помочь

[freelancer 1 467]

какие вижу варианты пока не получил explain

1. проверить скорость если убрать подзапрос

(SELECT os.name FROM " . DB_PREFIX . "order_status os WHERE os.order_status_id = o.order_status_id AND os.language_id = '" . (int)$this->config->get('config_language_id') . "') AS status

2. проверить есть ли ключи на полях из where, из sort order.

выборка из одной таблицы не должна тормозить

[markimax 1 922]

Ответ прост

 

LIKE '%" . $this->db->escape($data['filter_customer']) . "%'"

 

 

При % в "начале" LIKE - идет перебор всей базы

 

Это очень тормозной запрос и ни какие индексы не помогут

[nikifalex 495]

Смотря по какому полю фильтрация

[markimax 1 922]

Да там подзапрос при большой выборке еще может добавить "30`000" запросов к базе  :)

[freelancer 1 467]

Ответ прост

 

 

При % в "начале" LIKE - идет перебор всей базы

 

Это очень тормозной запрос и ни какие индексы не помогут

а как же FULLTEXT индекс ? 

[freelancer 1 467]

Да там подзапрос при большой выборке еще может добавить "30`000" запросов к базе  :)

если дело в подзапросе, то проблема легко решается его заменой на JOIN.

без expalin'а мы можем только гадать )

[markimax 1 922]

а как же FULLTEXT индекс ? 

Для %...% до 3.14 он

Только для LIKE ...%

[freelancer 1 467]

я имел ввиду замена like на MATCH (col1,col2,...) AGAINST (expr [search_modifier])

[markimax 1 922]

я имел ввиду замена like на MATCH (col1,col2,...) AGAINST (expr [search_modifier])

А, ну да, если все менять так и подзапрос на JOIN и на  MATCH поменять LIKE %

[halfhope 195]

Сам запрос

SELECT o.order_id, CONCAT( o.lastname, ' ', o.firstname ) AS customer, o.order_status_id, o.webme_sent_review_reminder, (SELECT os.name FROM order_status os WHERE os.order_status_id = o.order_status_id AND os.language_id = '1') AS status, o.total, o.currency_code, o.currency_value, o.date_added, o.date_modified FROM `order` o WHERE o.order_status_id = '24' ORDER BY o.order_id DESC LIMIT 0,50

Выполняется за 1.4 секунды с включенным профайлером, что вполне приемлемо. Вот что выдал профайлер: Total time 13.7506 seconds | Total queries:563

 

Проблема была в vqmod/ocmod модулях для показа реферала (soforp_order_referer) и цвета заказа, они добавляют подзапросы:

SELECT referrer FROM `order_referrer` WHERE order_id = '29622' and cookie_name='last_referrer'

и

SELECT color FROM order_status_color WHERE order_status_id = '24'

Каждый из этих запросов выполняется в среднем за 0.15 сек. Авторам данных дополнений уже отписался. Кстати, отключили soforp_order_referer и заработало шустрей (1.5 сек). 

 

Решение

 

Проблема решилась без отключения модуля soforp_order_referer, путем добавления индексов  в таблицу order_referrer

 

order_referrer_id  int(11) AI

order_id                int(11) NULL  

cookie_name        varchar(100) NULL  

cookie_val            int(11) NULL  

referrer                 text NULL

 

Добавил индексы в поля по которым идет выборка:

ALTER TABLE `order_referrer`
ADD INDEX `order_id` (`order_id`);

ALTER TABLE `order_referrer`
ADD INDEX `cookie_name` (`cookie_name`);

С отключенным профайлером загрузка страницы встала на место и теперь занимает всего ~0.6 секунд. Всем спасибо за потраченное время. Спасибо, кстати, не знал про замену LIKE.

Изменено 16 сентября, 2015 пользователем halfhope

[markimax 1 922]

Total queries:563

 

ОГО! :?

[markimax 1 922]

знал про замену LIKE.

 

 

Тоже аккуратно у MATCH свои подводные камни есть

Так что панацеи нет для "поиска" по тексту полноценного

 

Один вариант лучше на одних задачах, другой на других

 

Универсального решения - НЕТ :(

[freelancer 1 467]

Тоже аккуратно у MATCH свои подводные камни есть

Так что панацеи нет для "поиска" по тексту полноценного

 

Один вариант лучше на одних задачах, другой на других

 

Универсального решения - НЕТ :(

всё верно.

MATCH (col1,col2,...) AGAINST (expr [search_modifier]) это не замена LIKE, это функция, которая, возможно, при каких-то условиях будет работать быстрее чем LIKE

[freelancer 1 467]

Проблема решилась без отключения модуля soforp_order_referer, путем добавления индексов  в таблицу order_referrer

 

пробуйте заменить подзапросы на LEFT JOIN, возможно еще скорости добавится

[neoseo 132]

Опенкарт такая штука, что либо будет 100500 запросов, либо будет проблема с совместимостью с модулями ( когда патчится основной запрос ). Я вдоволь наелся проблем второго типа, когда сделал скидку на модули, и потому перешел на первую модель, когда на каждый заказ вытягиваются доп данные. По совместимости стало идеально. Но не учел что табличка у меня сама по себе не маленькая, да и есть клиенты, которые вместо 10 заказов жаждут видеть сразу 100.

 

Поэтому огромное спасибо за дружеский пинок по индексам. От себя добавлю что индекс нужен был покрывающий, т.е. такой:

$this->db->query("ALTER TABLE `" . DB_PREFIX . "order_referrer` ADD INDEX `order` (`order_id`,`cookie_name`);");

Новая версия сейчас будет опубликована, а владельцам старой придется выполнить запрос ручками.

[AlexDW 1 150]

Новая версия сейчас будет опубликована, а владельцам старой придется выполнить запрос ручками.

а разве нельзя сделать проверку на наличие индекса и если нет, добавлять автоматом?

 

PS: сам еще не сталкивался с проверкой по наличию индексов - если что не так, сапогами прошу не кидать :ugeek:

[markimax 1 922]

а разве нельзя сделать проверку на наличие индекса и если нет, добавлять автоматом?

 

PS: сам еще не сталкивался с проверкой по наличию индексов - если что не так, сапогами прошу не кидать :ugeek:

Alex там не только проверка на индексы- там проблема в запросе - очень большая выборка получается - более 500 подзапросов :(

Там сам запрос надо менять перехватив модель и обработать через __call

[AlexDW 1 150]

Там сам запрос надо менять перехватив модель и обработать через __call

- дяденька.. я не настоящий сварщик! (с) :-D

 

сорри за офтоп

просто стало интересно - зачем выносить мозг пользователям ручным выполнением запроса в БД, если можно добавить автоматом?

или нельзя?

 

ну-ка пойду тестить! :ugeek:

[freelancer 1 467]

AlexDW

    private $fields = array(
        'product_attribute' => array('product_id'),
        'product_option_value' => array('option_value_id', 'product_id'),
        'product_to_category' => array('category_id'));

...

    public function check_indexes() {
        foreach($this->fields as $table => $indexes) {
            $query = $this->db->query("SHOW INDEX FROM `" . DB_PREFIX . $table . "`");

            $keys = array();
            foreach($query->rows as $row) {
                    $keys[] = $row['Column_name'];
            }
            $keys = array_diff($indexes, $keys);
            foreach($keys as $key) {
                $this->db->query("ALTER TABLE `" . DB_PREFIX . $table . "` ADD INDEX ( `" . $key . "` )");
            }
        }
    }

[neoseo 132]

а разве нельзя сделать проверку на наличие индекса и если нет, добавлять автоматом?

 

PS: сам еще не сталкивался с проверкой по наличию индексов - если что не так, сапогами прошу не кидать :ugeek:

Со временем туго :) В следующих версиях обязатлеьно сделаю

 

> просто стало интересно - зачем выносить мозг пользователям ручным выполнением запроса в БД, если можно добавить автоматом?

Все правильно, но кому это реально мешает - попросят меня в личку и я выполню им запросы. кому не мешает, тот и заморачиваться не будет

[neoseo 132]

freelancer, спасибо!

[AlexDW 1 150]

работает, тестил так:

    $sql = "SHOW INDEX FROM " . DB_PREFIX ."product WHERE KEY_NAME = 'productz'";
    $query = $this->db->query($sql);
    if ($query->num_rows == 0) {
        $this->db->query("ALTER TABLE " . DB_PREFIX ."product ADD INDEX `productz` (`product_id`,`cost`)");
    }

freelancer - спасибо за решение!

[markimax 1 922]

работает, тестил так:

    $sql = "SHOW INDEX FROM " . DB_PREFIX ."product WHERE KEY_NAME = 'productz'";
    $query = $this->db->query($sql);
    if ($query->num_rows == 0) {
        $this->db->query("ALTER TABLE " . DB_PREFIX ."product ADD INDEX `productz` (`product_id`,`cost`)");
    }

freelancer - спасибо за решение!

 

 

Можно так еще

        $r = $this->db->query("SHOW KEYS FROM `" . DB_PREFIX . "review_fields` WHERE Key_name ='review_id'");
        if ($r->num_rows == 0) {
            $msql = "ALTER TABLE `" . DB_PREFIX . "review_fields`  ADD INDEX (`review_id`)";
            $query = $this->db->query($msql);
        }