Помогите ускорить запрос выборки/подсчета товаров

[eugeneledenev]

SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS product_id 
FROM(SELECT DISTINCT p.product_id, pd.name, p.model, p.quantity, p.price, p.sort_order, p.date_added , p.price as realprice 
FROM oc_product p LEFT JOIN oc_product_option_value pov ON (pov.product_id=p.product_id) 
LEFT JOIN oc_product_description pd ON (pd.product_id=p.product_id) 
LEFT JOIN oc_product_to_category p2c ON (p2c.product_id=p.product_id) 
LEFT JOIN oc_category_path cp ON (cp.category_id=p2c.category_id) 
WHERE 1 AND cp.path_id IN (3558) AND pd.language_id = '1' AND p.status = '1' ) 
as innertable WHERE 1 ORDER BY sort_order ASC, LCASE(name) ASC LIMIT 0,20

oc_store 1.5.5, 100 тыс товаров, 4 тыс категорий. Выполняется 1-1,5сек. Как можно ускорить?

Пробовал:

Разделить запрос на 2 как в стандартном опенкарт(отдельно счетчик количества товаров в категории, отдельно выборка товаров). Плюс сделал таблицу в БД sort_id, product_id в которую для каждого типа сортировки загнал отсортированные id товаров, тем самым избавился от ORDER BY. Время выборки товаров уменьшилось до 0,15сек, но второй запрос который считает товары работает 0,7сек. Пробовал его и через count и тупо вывод всех данных без count, результат плюс-минус идентичный.

• Либо для подсчета товаров использовать кешированные данные, но запрос который мне выдал бы список всех id категорий и кол-ва товаров в них(с товарами подкатегорий) я написать так и не смог.
• Либо отказываться от пагинации в текущем виде и делать только кнопки следующая/предыдущая.
• Либо (на что я очень надеюсь) я лошара и все можно сделать намного проще и быстрее.

[chukcha]

Так ли вам нужен подсчет товаров?
Нужна ли сортировка по цене

 

    LEFT JOIN oc_product_option_value pov ON (pov.product_id=p.product_id) 

НАХУА?
Или это  тест на внимательность?

[markimax]

LEFT JOIN oc_category_path cp ON (cp.category_id=p2c.category_id)
..
AND cp.path_id IN (3558)

Проверьте на индексы еще здесь

[snastik]

33 минуты назад, eugeneledenev сказал:

SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS product_id 
FROM(SELECT DISTINCT p.product_id, pd.name, p.model, p.quantity, p.price, p.sort_order, p.date_added , p.price as realprice 
FROM oc_product p LEFT JOIN oc_product_option_value pov ON (pov.product_id=p.product_id) 
LEFT JOIN oc_product_description pd ON (pd.product_id=p.product_id) 
LEFT JOIN oc_product_to_category p2c ON (p2c.product_id=p.product_id) 
LEFT JOIN oc_category_path cp ON (cp.category_id=p2c.category_id) 
WHERE 1 AND cp.path_id IN (3558) AND pd.language_id = '1' AND p.status = '1' ) 
as innertable WHERE 1 ORDER BY sort_order ASC, LCASE(name) ASC LIMIT 0,20

oc_store 1.5.5, 100 тыс товаров, 4 тыс категорий. Выполняется 1-1,5сек. Как можно ускорить?

Пробовал:

Разделить запрос на 2 как в стандартном опенкарт(отдельно счетчик количества товаров в категории, отдельно выборка товаров). Плюс сделал таблицу в БД sort_id, product_id в которую для каждого типа сортировки загнал отсортированные id товаров, тем самым избавился от ORDER BY. Время выборки товаров уменьшилось до 0,15сек, но второй запрос который считает товары работает 0,7сек. Пробовал его и через count и тупо вывод всех данных без count, результат плюс-минус идентичный.

• Либо для подсчета товаров использовать кешированные данные, но запрос который мне выдал бы список всех id категорий и кол-ва товаров в них(с товарами подкатегорий) я написать так и не смог.
• Либо отказываться от пагинации в текущем виде и делать только кнопки следующая/предыдущая.
• Либо (на что я очень надеюсь) я лошара и все можно сделать намного проще и быстрее.

 

За деньги)))

Пишите в личку.

[ZeroHero]

Конкретно данный запрос можно разбить на 3 простых, при этом лишнее выкинуть.

SELECT category_id INTO @a FROM oc_category_path WHERE path_id = 3558 LIMIT 1

Этот для подсчёта количества:

SELECT COUNT(p.product_id)
FROM oc_product_to_category AS p2c
INNER JOIN oc_product AS p
ON
    p2c.product_id = p.product_id AND
    p.status = 1
WHERE
    p2c.category_id = @a

Этот для выборки ID-шников:

SELECT p.product_id
FROM oc_product_to_category AS p2c
INNER JOIN oc_product AS p
ON
    p2c.product_id = p.product_id AND
    p.status = 1
WHERE
    p2c.category_id = @a
LIMIT 0, 20

А как оно там у вас реализовано и позволяет ли логика работы магазина - это уже на ваше усмотрение.

[chukcha]

1 минуту назад, markimax сказал:

Проверьте на индексы еще здесь

Сильно сомневаюсь что поможет, там

cp.path_id IN (

[markimax]

40 минут назад, eugeneledenev сказал:

SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS product_id 
FROM(SELECT DISTINCT p.product_id, pd.name, p.model, p.quantity, p.price, p.sort_order, p.date_added , p.price as realprice 
FROM oc_product p LEFT JOIN oc_product_option_value pov ON (pov.product_id=p.product_id) 
LEFT JOIN oc_product_description pd ON (pd.product_id=p.product_id) 
LEFT JOIN oc_product_to_category p2c ON (p2c.product_id=p.product_id) 
LEFT JOIN oc_category_path cp ON (cp.category_id=p2c.category_id) 
WHERE 1 AND cp.path_id IN (3558) AND pd.language_id = '1' AND p.status = '1' ) 
as innertable WHERE 1 ORDER BY sort_order ASC, LCASE(name) ASC LIMIT 0,20

• Либо отказываться от пагинации в текущем виде и делать только кнопки следующая/предыдущая.
•  

Сделайте как у google и т п
"Окно" пагинации "вперед". Т е не полный лист пагинации
Т е запрос например на 3-5 страниц вперед (все равно никто не "лётает" сразу на последнюю) с LIMIT
У вас слишком большая выборка получается и джойниться потом большое количество обьектов (отсюда и тормоза)
Ограничьте LIMIT - м окна и количество
Так делают пагинацию в нагруженных проектах
Здесь к запросу нужно подходить не методом тыка а логически думать -"почему"

[eugeneledenev]

13 минут назад, chukcha сказал:

Так ли вам нужен подсчет товаров?
Нужна ли сортировка по цене

 

    LEFT JOIN oc_product_option_value pov ON (pov.product_id=p.product_id) 

НАХУА?
Или это  тест на внимательность?

Без подсчета товаров не будет работать пагинация.

Сортировки хотелось бы оставить.

По таблице pov - она пустая, тесты показали, что на скорость не влияет, поэтому пока что не вычистил ее из запроса.

[eugeneledenev]

25 минут назад, markimax сказал:

LEFT JOIN oc_category_path cp ON (cp.category_id=p2c.category_id)
..
AND cp.path_id IN (3558)

Проверьте на индексы еще здесь

Есть индексы. http://prntscr.com/ebifc6  и  http://prntscr.com/ebifjr  - во второй правда какой-то двойной индекс по category_id. Не уверен что так должно быть.

Тормоза в запросах начинаются как только в EXPAIN появляется надпись using temporary - это сортировки, count.

Змінено 21 лютого 2017 користувачем eugeneledenev

[eugeneledenev]

20 минут назад, markimax сказал:

Сделайте как у google и т п
"Окно" пагинации "вперед". Т е не полный лист пагинации
Т е запрос например на 3-5 страниц вперед (все равно никто не "лётает" сразу на последнюю) с LIMIT
У вас слишком большая выборка получается и джойниться потом большое количество обьектов (отсюда и тормоза)
Ограничьте LIMIT - м окна и количество
Так делают пагинацию в нагруженных проектах
Здесь к запросу нужно подходить не методом тыка а логически думать -"почему"

Это делается выборкой вместо LIMIT 20,20 я ставлю LIMIT 19,101  (101=20+20*4страницы+1) и смотрю сколько товаров вернул, верно? Ну и потом прибиваю первый и последний товар. Ну и лишние товары тоже.

 

Змінено 21 лютого 2017 користувачем eugeneledenev

[markimax]

3 часа назад, eugeneledenev сказал:

Это делается выборкой вместо LIMIT 20,20 я ставлю LIMIT 19,101  (101=20+20*4страницы+1) и смотрю сколько товаров вернул, верно? Ну и потом прибиваю первый и последний товар. Ну и лишние товары тоже.

 

Google даже этим не заморачивается
Смотрите как сделано
У google давно есть статистика как народ ведет себя на пагинации - смотрит ближайшие страницы
А добраться до первой через пару кликов окон тоже легко
Но в принципе можете первую "поставить", последнюю не зачем - все равно сразу на последнюю никто не ходит - психология, а первую и вычислять не надо просто URL - без параметров пагинации
Правда придется чуть переделать библиотеку пагинации - но это мелочь на нагруженных проектах

https://www.google.com/search?q=opencart&ie=utf-8&oe=utf-8&gws_rd=cr&ei=G1esWJi1J8mzsQGKr5nQCA#q=opencart&start=100

[eugeneledenev]

Решил, может кому пригодится. Увеличение скорости в 10 раз(до 0,15сек)

Изменил вывод пагинации, убрал подсчет кол-ва товаров в текущей категории.

В запросе :

Скрытый текст

            $sql .= "AND ps.sort_id='".$sort_id."'";
            
            if(isset($data['start']) || isset($data['limit'])) {
                $limit=$data['limit'];
                $data['start'] = $data['start']-1;
                $data['limit'] = $data['limit']*5+1;
                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);
            
            $product_data = array();
            if($query->rows) {
                $this->load->model('catalog/product');
                if ($data['start'] == 0) {
                    $counter = 1;
                } else {
                    $counter = 0;
                }
                foreach($query->rows as $result) {
                    If ($counter!=0 && $counter <=$limit) {
                        $product_data[$result['product_id']] = $this->model_catalog_product->getProduct($result['product_id']);
                    }
                    $counter++;
                }
                $this->FOUND_ROWS = $counter-1+$data['start']+1;
            }

            return $product_data;
        }

 

В категории оставляем все так же как и было

Скрытый текст

            $results = $this->model_catalog_combination->getProducts($data);
                        //Вызов метода getFoundProducts должен проводится сразу же после getProducts
            //только тогда он выдает правильное значения количества товаров
                        $product_total = $this->model_catalog_combination->getFoundProducts();

 

Немного комментим/меняем пагинацию

Скрытый текст

    public function render() {
        $total = $this->total;
        
        if ($this->page < 1) {
            $page = 1;
        } else {
            $page = $this->page;
        }
        
        if (!(int)$this->limit) {
            $limit = 10;
        } else {
            $limit = $this->limit;
        }
                
        $num_links = 4; //$this->num_links;
        $num_pages = ceil($total / $limit);
        
        $output = '';
        
        if ($page > 1) {
            $output .= ' <a href="' . str_replace('{page}', 1, $this->url) . '">' . $this->text_first . '</a> <a href="' . str_replace('{page}', $page - 1, $this->url) . '">' . $this->text_prev . '</a> ';
        }

        if ($num_pages > 1) {
            if ($num_pages <= $num_links) {
                $start = 1;
                $end = $num_pages;
            } else {
                $start = $page - floor($num_links / 2);
                $end = $page + floor($num_links / 2);
            
                if ($start < 1) {
                    $end += abs($start) + 1;
                    $start = 1;
                }
                        
                if ($end > $num_pages) {
                    $start -= ($end - $num_pages);
                    $end = $num_pages;
                }
            }

            if ($start > 1) {
                $output .= ' .... ';
            }

            for ($i = $start; $i <= $end; $i++) {
                if ($page == $i) {
                    $output .= ' <b>' . $i . '</b> ';
                } else {
                    $output .= ' <a href="' . str_replace('{page}', $i, $this->url) . '">' . $i . '</a> ';
                }    
            }
                            
            if ($end < $num_pages) {
                $output .= ' .... ';
            }
        }
        
//           if ($page < $num_pages) {
//            $output .= ' <a href="' . str_replace('{page}', $page + 1, $this->url) . '">' . $this->text_next . '</a> <a href="' . str_replace('{page}', $num_pages, $this->url) . '">' . $this->text_last . '</a> ';
//        }
        
        $find = array(
            '{start}',
            '{end}',
            '{total}',
            '{pages}'
        );
        
        $replace = array(
            ($total) ? (($page - 1) * $limit) + 1 : 0,
            ((($page - 1) * $limit) > ($total - $limit)) ? $total : ((($page - 1) * $limit) + $limit),
//            $total, 
//            $num_pages
                        '',
                        ''
        );
        
        return ($output ? '<div class="' . $this->style_links . '">' . $output . '</div>' : '') . '<div class="' . $this->style_results . '">' . str_replace($find, $replace, $this->text) . '</div>';
    }

 

Результат:

 

Итоговый запрос выглядит так:

SELECT DISTINCT ps.product_id FROM oc_product_sort ps LEFT JOIN oc_product p ON (ps.product_id=p.product_id) LEFT JOIN oc_product_to_category p2c ON (p2c.product_id=ps.product_id) LEFT JOIN oc_category_path cp ON (cp.category_id=p2c.category_id) WHERE 1 AND cp.path_id IN (3587) AND p.status = '1' AND ps.sort_id='1' LIMIT 219,101

В этом запросе таблица ps - это отсортированные id товаров. Сейчас пришла идея убрать оттуда LEFT JOIN oc_product p ON (ps.product_id=p.product_id) и AND p.status = '1'. и засунуть это в формировании таблицы ps.

 

И если кому интересно, формирование таблицы ps

Скрытый текст

   private function FillProductSort($sort,$order) {
        switch ($sort.$order) {
            case "p.sort_orderASC":
                $sort_id=1;
                break;
            case "pd.nameASC":
                $sort_id=2;
                break;
            case "pd.nameDESC":
                $sort_id=3;
                break;
            case "p.priceASC":
                $sort_id=4;
                break;
            case "p.priceASC":
                $sort_id=5;
                break;
            case "pd.nameDESC":
                $sort_id=6;
                break;
            case "p.modelASC":
                $sort_id=7;
                break;            
            case "p.modelDESC":
                $sort_id=8;
                break;  
            default:
                return 0;
                break;  
            }
//cache start
        $cache_data  = $this->cache->get('fillproductsort_id'.$sort_id);
        if (!empty($cache_data)) {
            return $sort_id;
        } else {			
//cache start
            $sql="DELETE FROM oc_product_sort WHERE sort_id='".$sort_id."' ";    
            $query = $this->db->query($sql);
            $sql="INSERT INTO oc_product_sort(SELECT DISTINCT '".$sort_id."'as sort_id, p.product_id FROM oc_product p LEFT JOIN oc_product_description pd ON (pd.product_id=p.product_id) WHERE 1";

            if($sort == 'name' || $sort == 'model') {
                $sql .= " ORDER BY LCASE(" . $sort . ")";
            } else {
                $sql .= " ORDER BY " . $sort;
            }
            if($order == 'DESC') {
                $sql .= " DESC, LCASE(name) DESC";
            } else {
                $sql .= " ASC, LCASE(name) ASC";
            }
            $sql .= ")";
            $query = $this->db->query($sql);
//cache end
        }
        $cache_data = "table is fill";
        $expire = 60*60*24*1;//Время жизни кеша 1 сутки - 60*60*24*1
        $this->cache->set('fillproductsort_id'.$sort_id, $cache_data, $expire);
//cache end
        
        return $sort_id;
    }

 

 

Змінено 25 лютого 2017 користувачем eugeneledenev

[eugeneledenev]

>Сейчас пришла идея убрать оттуда LEFT JOIN oc_product p ON (ps.product_id=p.product_id) и AND p.status = '1'. и засунуть это в формировании таблицы ps. 

Выполнил, время выполнения упало до 0,04сек.

 

[Yoda]

10 часов назад, eugeneledenev сказал:

>Сейчас пришла идея убрать оттуда LEFT JOIN oc_product p ON (ps.product_id=p.product_id) и AND p.status = '1'. и засунуть это в формировании таблицы ps. 

Выполнил, время выполнения упало до 0,04сек.

 

Совершенно неправильный подход.
Посредством денормализации базы и построения flat-таблиц можно добиться молниеносных результатов, но вы тем самым уходите от нативной структуры и убиваете любую возможность масштабирования системы.

 

То что вы выше привели - оптимизировать посредством правильных индексов в базу- просто семечки.
Просто немного нужно взять и почитать мануал к mysql

[chukcha]

LEFT JOIN oc_product p ON (ps.product_id=p.product_id) и AND p.status = '1'

Где вы взяли такой код, а именно таблицу

 oc_product_sort ps

 

 

 

 

 

[Yoda]

Только что, chukcha сказал:

LEFT JOIN oc_product p ON (ps.product_id=p.product_id) и AND p.status = '1'

Где вы взяли такой код, а именно таблицу

 oc_product_sort ps

 

 

 

 

 

Да где, сделал он ее!!!
А механизм апдейта забыл)))

И про составные индексы тоже забыл)

ШКОЛОЛО и ФАНАТ рухайлоад детектед.

[chukcha]

56 минут назад, Yoda сказал:

ШКОЛОЛО и ФАНАТ р

Ну.. мы все не без греха - по крайней мере попытка, а это уже хорошо.

Тут проблема такая, что только или на своем опыте, спотыкаясь и читая
Или на чужом, но при этом нужно платить за знания.

[Yoda]

9 минут назад, chukcha сказал:

Ну.. мы все не без греха - по крайней мере попытка, а это уже хорошо.

Тут проблема такая, что только или на своем опыте, спотыкаясь и читая
Или на чужом, но при этом нужно платить за знания.

 

Могу возразить!
Данная ситуация находится намного выше плоскости "тыжпрограммист". 
Понимание данных технологии, глубокое понимание возможно исключительно при наличии глубокой общей базы. 
Все попытки "на лоха" выведать секреты и въехать на блатной козе в "быстросайт" обречены на провал.
Учиться учиться и еще раз учиться, или платить платить платить!

 

Можете меня называть как угодно и кем угодно, но я не готов опыт и знания, полученные на протяжении нескольких лет изысканий раздавать бесплатно!

[chukcha]

16 минут назад, Yoda сказал:

Можете меня называть как угодно и кем угодно, но я не готов опыт и знания, полученные на протяжении нескольких лет изысканий раздавать бесплатно!

Так  и я  об этом

31 минуту назад, chukcha сказал:

Или на чужом, но при этом нужно платить за знания.

 

[Yoda]

7 минут назад, chukcha сказал:

Так  и я  об этом

 

Так выпьем же за понимание!
И еще раз подчеркну: денормализация структуры данных - это дурной тон bad practice и не выход!
Учите матчасть господа, прежде чем задаваться вопросами "помогите оптимизировать" и "я тут на коленке сваял".

[eugeneledenev]

>Совершенно неправильный подход.

Нет в этой жизни хорошо, плохо, правильно, неправильно. Это все относительно в наших головах

>То что вы выше привели - оптимизировать посредством правильных индексов в базу- просто семечки.
>Просто немного нужно взять и почитать мануал к mysql

Когда я просил помощи от Вас никакой конкретики не получил. Только " Можете меня называть как угодно и кем угодно, но я не готов опыт и знания, полученные на протяжении нескольких лет изысканий раздавать бесплатно!" Сейчас вы пишите, что все криво, но опять же без конкретики. По делу:

Выборка о которой я писал сортирует и считает порядка 18 тыс товаров и SQL_CALC_FOUND_ROWS(он же COUNT), ORDER BY sort_order ASC, LCASE(name) ASC LIMIT 0,20 убивают скорость. Без подсчета кол-ва товаров и сортировок все летает. Мне очень сильно кажется, что с помощью индексов оптимизировать этот запрос невозможно.

>Да где, сделал он ее!!!
>А механизм апдейта забыл)))

Механизм апдейта есть. Выложена сама функция. И поскольку ветка для программистов я решил что вызов этой функции кому надо сможет сделать сам.

 

Теперь самое интересное: про "за деньги".

Я руководитель и постоянно ищу исполнителей на те или иные работы. Программиста, который считает свою зарплату из расчета >60...80тыс руб в мес я не вытягиваю. Даже в этой ветке люди видят порядка 4 решений проблемы и почти любой подход имеет свои недостатки. Найти нормального программиста не просто. Люди считают, что они знают что делать, берут бабки и у них не получается решить задачу, а поскольку работы есть, то они предпочитают делать кнопки обратного звонка, лендинги и т.п. несложные задачи. На моей практике не было ни одного программиста, который мог бы нормально проверить результат своей работы, то есть сдать код без ошибок. Все равно рано или поздно что-то вылезает. Одно починил, другое сломал, т.к. никому неохота разбираться что тут до него делали и зачем. Так же проблема с нехваткой времени у тех кто работает за адекватные деньги, тебя записывают на "через месяц" и позже. Ну и само собой как всегда любой программист говорит что до него тут работал рукожопый дебил (точно так же делают стоматологи "да у вас все криво сделано, давайте ВСЕ переделаем"). Либо ответ на замечание: "Для этого нужно весь OPENCART переписать", хотя как оказывается позже 1 час работы несильно квалифицированного программиста.

Очень часто мы думаем, что знаем как решить задачу, а когда начинаем решать всплывает всякая фигня и решение оказывается не таким простым.

 

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

[Otvet]

В рамках своего проекта решение может быть верным и оправданным
Но для масс не будет подходить ввиду нарушения совместимости