Оптимизация pagination

@Именч · Регистрация: 03.09.2018

Студворк — интернет-сервис помощи студентам

Имею сайт, на нём ~500 тысяч товаров. 2500 тысяч категорий. Изначально он был реализован на opencart, но как показала практика, opencart умирает. По 20 секунд на загрузку каталога минимально. Реализовал полностью свой собственный движок, админку, и так далее. Удалось реализовать загрузку страниц до 3 секунд на каталоге. После анализа, пришло понимание, где зарыта проблема, до священной загрузки до 1 секунды

Самый простой, банальный вариант 2.6530 - 3.2346 сек.

SQL
1
2
3
4
5
6
7
SELECT 
  COUNT(`products`.`id`) AS max_count 
FROM 
  `products`
  JOIN `category_list_product` ON `products`.`id` = `category_list_product`.`product_id` 
WHERE 
  `category_list_product`.`category_id` = 12786

Пытаемся обыграть, расчёт count на каждый тик (нет понимания точного как работает)

Такой вариант 0.9345 - 1.3235 сек.

SQL
1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
  COUNT(*) AS max_count 
FROM 
  (
    SELECT 
      `products`.`id` 
    FROM 
      `products` 
      JOIN `category_list_product` ON `products`.`id` = `category_list_product`.`product_id` 
    WHERE 
      `category_list_product`.`category_id` = 12786
  ) AS unique_rows

Собственно, почему я уверен что оно может быстрее ? Вполне хороший вопрос, на который можно ответить вот так

Запрос занял 0.0098 сек.

SQL
1
2
3
4
5
6
7
SELECT 
  `products`.`id` AS max_count 
FROM 
  `products`
  JOIN `category_list_product` ON `products`.`id` = `category_list_product`.`product_id` 
WHERE 
  `category_list_product`.`category_id` = 12786

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

Есть у кого какой опыт, идеи ? Как можно попытаться обыграть этот count производительнее

Сервер: Localhost via UNIX socket
Тип сервера: MySQL
Версия сервера: 8.0.35-0ubuntu0.22.04.1 - (Ubuntu)
Версия протокола: 10

nginx/1.20.2
Версия клиента базы данных: libmysql - mysqlnd 7.4.33
PHP расширение: mysqli Документация curl Документация mbstring Документация
Версия PHP: 7.4.33

Сам сайт работает на PHP 8.2

Добавлено через 16 минут
Запрос занял 0.1423 сек. Но пока непонятно как это можно засунуть в mysqli, не даёт выполнить

SQL
1
2
3
4
5
6
7
8
9
SET @index_max := 0;
 
SELECT 
  COUNT(@index_max = @index_max + 1) AS max_count 
FROM 
  `products`
  JOIN `category_list_product` ON `products`.`id` = `category_list_product`.`product_id` 
WHERE 
  `category_list_product`.`category_id` = 12786

Есть ещё идеи ?

@PaulWist · 29.11.2023, 11:45

Сообщение от Именч

Хостинг послал нафиг с таким запросом.

Это ожидаемо.

У меня вопрос, нафига в запросе вяжется табличка products, ведь из неё ничего не вынимается, упростите запрос

MySQL
1
2
3
4
5
6
SELECT 
  COUNT(*) AS max_count 
FROM 
`category_list_product` 
WHERE 
  `category_list_product`.`category_id` = 12786

@Именч · 29.11.2023, 17:34 **[ТС]**

Сообщение от PaulWist

ведь из неё ничего не вынимается

Это упрощённый запрос. Там ещё фильтры есть

Добавлено через 2 часа 27 минут

Сообщение от PaulWist

У меня вопрос, нафига в запросе вяжется табличка products, ведь из неё ничего не вынимается, упростите запрос

Я так подумал, действительно. Можно же кинуть JOIN на products и уже этот JOIN фильтровать.

Годный совет

@PaulWist · 29.11.2023, 17:42

Сообщение от Именч

Можно же кинуть JOIN на products и уже этот JOIN фильтровать.

Я бы ещё, для общности пейзажа (не поможет, но архитектурно правильно) переписал

SQL
1
2
3
4
5
FROM 
  `products`
  JOIN `category_list_product` ON `products`.`id` = `category_list_product`.`product_id` 
WHERE 
  `category_list_product`.`category_id` = 12786

на

SQL
1
2
3
4
5
FROM 
  `products`
  JOIN `category_list_product` ON `products`.`id` = `category_list_product`.`product_id` 
AND
  `category_list_product`.`category_id` = 12786

@Именч · 29.11.2023, 22:38 **[ТС]**

На забагованной БД скорость заняла 0.072361 сек. Это огромный успех

На нормальной, занимает 0.017522 сек. Разница в 0.054839 !!!

Добавлено через 51 минуту
Пришлось конечно попатеть над запросом, но оно того стоила! Каталог открывается за 400 мс

Кликните здесь для просмотра всего текста

(Урезанный код, тут ещё полно фильтр-обработок)

PHP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
function buildProductQuery($connect, array $filter, $isCount, $castom = false)
{
    $debugSpeed = false;
 
    $filter_name_query = '';
    if (isset($filter['filter_name']) && ($filter_name = trim(preg_replace("/\s+/", " ", $filter['filter_name'])))) {
        $filter_name2 = $connect->escape($filter_name);
        $filter_nameREGEXP = preg_quote($filter_name2, '/');
 
        $buildArray = ["(`rooms`.`name` REGEXP '" . $filter_nameREGEXP . "')"];
 
        $filter_name = str_replace(['.', ',', '"', '\'', '!', '@', '#', '$', '%', '^', '&', '*', '(', ')', '_', '+', '-', '='], ' ', $filter_name);
        
        if (trim($filter_name) != trim($filter_name2)) {
            $filter_name = explode(' ', trim(preg_replace("/\s+/", " ", $filter_name)));
            
        
            $values = '';
            foreach ($filter_name as $v) {
                if ($values) {
                    $values .= '|';
                }
                $values .= preg_quote($connect->escape(trim($v)), '/');
            }
            
            $buildArray[] = "(`rooms`.`name` REGEXP '" . $values . "')";
        }
        $filter_name_query = ' (' . implode(' OR ', $buildArray) . ') ';
    }
 
    $filters_query = '';
 
    if (isset($filter['filters']) && count($filter['filters'])) {
        $buildArray = [];
        foreach ($filter['filters'] as $filter_value) {
            $buildArray[] = "(`products`.`filter` LIKE '%" . $connect->escape($filter_value) . "%')";
        }
        $filters_query .= ' (' . implode(' OR ', $buildArray) . ') ';
    }
 
    $WHERE = '';
    if (isset($filter['sort'])) {
        $polesSort = [
            'no' => (isset($filter_nameREGEXP) && $filter_nameREGEXP) ? '`products`.`name` REGEXP \'' . $filter_nameREGEXP . '\' DESC' : '',
            'price' => '`products`.`priceMarkup`',
            'date' => '`products`.`dateSetTime` DESC',
            'popularity' => '`products`.`popularity`'
        ];
 
        $polesSort = isset($polesSort[$filter['sort']]) ? trim($polesSort[$filter['sort']]) : false;
        if ($polesSort) {
            $WHERE .= " ORDER BY " . $polesSort;
        }
    }
 
    if ((!isset($filter['offset']) || $filter['offset'] === false) && (isset($filter['rowcount']) && $filter['rowcount'] !== false)) {
        $WHERE .= ' LIMIT ' . (int) $filter['rowcount'];
    } elseif ((isset($filter['offset']) && $filter['offset'] !== false) && (isset($filter['rowcount']) && $filter['rowcount'] !== false)) {
        $WHERE .= ' LIMIT ' . (int) $filter['offset'] . ', ' . (int) $filter['rowcount'];
    }
 
 
 
    if (isset($filter['category_id']) && (($category_id = (int) trim($filter['category_id'])) > 0)) {
        if (isset($filter['sort']) || $filter_name_query || $filters_query) {
            $addQuery = "JOIN `products` ON `products`.`id` = `category_list_product`.`product_id`";
 
            if ($filter_name_query) {
                $filter_name_query = ' AND ' . $filter_name_query;
            }
            if ($filters_query) {
                $filters_query = ' AND ' . $filters_query;
            }
        } else {
            $addQuery = '';
        }
 
        if ($isCount) {
            $SQL = "SELECT 
                COUNT(*) AS count_rows 
            FROM 
                `category_list_product` 
                {$addQuery}
            WHERE 
                (`category_list_product`.`category_id` = {$category_id}){$filter_name_query}{$filters_query}";
 
 
            $test = microtime(1);
            $queryResult = $connect->query($SQL);
            if ($debugSpeed) {
                printHtml([sprintf('время: %f', microtime(1) - $test), $SQL]);
            }
 
            if (queryGetValueRow($queryResult, $row)) {
                return $row['count_rows'];
            } else {
                return 0;
            }
        } else {
            if (!$castom) {
                $castom = '`products`.`id`, `products`.`sku`, `products`.`vendor`, `products`.`LineDescription`, `products`.`price`, `products`.`priceMarkup`, `products`.`name`, `products`.`picture`';
            }
 
            $SQL = "SELECT 
                `category_list_product`.`product_id` 
            FROM 
                `category_list_product` 
                {$addQuery}
            WHERE 
                (`category_list_product`.`category_id` = {$category_id}){$filter_name_query}{$filters_query}{$WHERE}";
 
            $test = microtime(1);
            $queryResult = $connect->query($SQL);
            if ($debugSpeed) {
                printHtml([sprintf('время: %f', microtime(1) - $test), $SQL]);
            }
 
            $filterResult = [];
            if (queryGetValue($queryResult, $rows, $num_rows)) {
                if ($num_rows) {
                    $ids = '';
                    foreach ($rows as $item) {
                        if ($ids)
                            $ids .= ',';
                        $ids .= $item['product_id'];
                        $filterResult[$item['product_id']] = false;
                    }
 
                    if (queryGetValue($connect->query("SELECT {$castom} FROM `products` WHERE `id` in ($ids)"), $rows2, $num_rows2)) {
                        foreach ($rows2 as $row) {
                            $filterResult[$row['id']] = $row;
                        }
                    }
                }
 
            }
            return $filterResult;
        }
    } else {
        if ($isCount) {
            if ($filter_name_query || $filters_query) {
                $WHERE = ' WHERE ';
                if ($filter_name_query) {
                    $WHERE .= $filter_name_query;
                }
                if ($filters_query) {
                    $WHERE .= ' AND ' . $filters_query;
                }
            } else {
                $WHERE = '';
            }
            $SQL = "SELECT COUNT(*) AS count_rows FROM (SELECT `products`.id FROM `products` {$WHERE}) AS unique_rows";
 
 
            $test = microtime(1);
            $queryResult = $connect->query($SQL);
            if ($debugSpeed) {
                printHtml([sprintf('время: %f', microtime(1) - $test), $SQL]);
            }
 
            if (queryGetValueRow($queryResult, $row)) {
                return $row['count_rows'];
            } else {
                return 0;
            }
        } else {
            if (!$castom) {
                $castom = '`products`.`id`, `products`.`sku`, `products`.`vendor`, `products`.`LineDescription`, `products`.`price`, `products`.`priceMarkup`, `products`.`name`, `products`.`picture`';
            }
 
            if ($filter_name_query || $filters_query) {
                $WHERE2 = ' WHERE ';
                if ($filter_name_query) {
                    $WHERE2 .= $filter_name_query;
                }
                if ($filters_query) {
                    $WHERE2 .= ' AND ' . $filters_query;
                }
            } else {
                $WHERE2 = '';
            }
 
            $SQL = " SELECT `products`.`id` FROM `products` {$WHERE2}{$WHERE}";
 
            $test = microtime(1);
            $queryResult = $connect->query($SQL);
            if ($debugSpeed) {
                printHtml([sprintf('время: %f', microtime(1) - $test), $SQL]);
            }
 
            $filterResult = [];
            if (queryGetValue($queryResult, $rows, $num_rows)) {
                if ($num_rows) {
                    $ids = '';
                    foreach ($rows as $item) {
                        if ($ids)
                            $ids .= ',';
                        $ids .= $item['id'];
                        $filterResult[$item['id']] = false;
                    }
 
                    if (queryGetValue($connect->query("SELECT {$castom} FROM `products` WHERE `id` in ($ids)"), $rows2, $num_rows2)) {
                        foreach ($rows2 as $row) {
                            $filterResult[$row['id']] = $row;
                        }
                    }
                }
 
            }
 
            return $filterResult;
        }
    }
}

время: 0.156133

MySQL
1
2
3
4
5
6
7
SELECT 
    COUNT(*) AS count_rows 
FROM 
    `category_list_product` 
    JOIN `products` ON `products`.`id` = `category_list_product`.`product_id`
WHERE 
    (`category_list_product`.`category_id` = 12786) AND  ((`products`.`name` REGEXP 'прод'))

время: 0.139981

MySQL
1
2
3
4
5
6
7
SELECT 
    `category_list_product`.`product_id` 
FROM 
    `category_list_product` 
    JOIN `products` ON `products`.`id` = `category_list_product`.`product_id`
WHERE 
    (`category_list_product`.`category_id` = 12786) AND  ((`products`.`name` REGEXP 'прод'))  ORDER BY `products`.`name` REGEXP 'прод' DESC LIMIT 0, 12

Добавлено через 2 часа 33 минуты
Вопрос. А могут ли неверные запросы ломать БД ? Я сейчас пытался оптимизировать LIMIT и кинул такой код

MySQL
1
2
3
4
5
6
7
SELECT * FROM `category_list_product` JOIN (SELECT 
                * 
            FROM 
                `category_list_product` 
                JOIN `products` ON `products`.`id` = `category_list_product`.`product_id`
            WHERE 
                (`category_list_product`.`category_id` = 12795) LIMIT 17875, 11) as b ON b.product_id = `category_list_product`.`product_id`

Первый запрос был долгий. И не к чему не привёл. Однако, через 3 выполнения запрос выполнился очень быстро. Но это не была заслуга такой оптимизации.

Я провёл выполнения

MySQL
1
2
3
4
5
6
7
explain SELECT 
  COUNT(*) AS max_count 
FROM 
  `products`
  JOIN `category_list_product` ON `products`.`id` = `category_list_product`.`product_id` 
WHERE 
  `category_list_product`.`category_id` = 12786

И результаты были хорошие. Прям как надо.
Я начал пользоваться сайтом снова. И через пару минут он снова начал лагать. Я перешёл, и проверил снова с explain всё поломалось.

Я выполняю снова 1 вариант JOIN SELECT и бд оживает через пару выполнений.

Что за чудеса ?

@Gluck99 · 29.11.2023, 22:49

Именч, Cash.

Новые блоги и статьи Все статьи Все блоги /
ИИ не может найти нужный язык в списке Supersumestria 05.10.2026 Я ему даю вот такое изображение и прошу найти и подчеркнуть немецкий язык. Возвращает он вот это: https:/ / i. **********/ vqBWLe2. png Нужную строчку в 3й колонке просто выдумал. . Это. . .	Новая последняя моя музыка в SUNO zorxor 05.10.2026 Здравствуйте, дорогие мои друзья! С большой радостью я хотел бы представить вам свою новую последнею музыку, которую сгенерировала мне по моей просьбе нейросеть SUNO. С уважением, zorxor. Это. . .	Программный домашний кинотеатр russiannick 27.09.2026 Сподобился на программный домашний кинотеатр. В качестве ЯВУ по традиции выбрал js. В помощники взял Яндекс-Алису. Было создано три зала на разные интересы. исторические и ретро сериал Хичкок. . .	Беседа с ИИ о программистах, недопускающих к созданию и правке кода генеративные ИИ и причины этого zorxor 21.09.2026 Раньше я радовался или получал некоторые эмоции, пусть небольшие, но всё же, от самого процесса написания кода, рекомпиляции и запуска, видя постепенное развитие программы и прочее. А теперь лень. . .
Мобильное приложение ColorStep pavlinmavlin 17.09.2026 Реализовал приложение Красный, Зеленый, Синий в Unity3d + c#. Название изменил на ColorStep. Приложение прошло модерацию и теперь доступно для скачивания. Делал его сам, шаг за шагом — и вот,. . .	Запрет дублирования строк в табличной части Maks 13.09.2026 Реализация из решения ниже выполнена на нетиповом справочнике "Нормы ТО" с табличной часть "Виды ТО", разработанного в КА2, со следующими реквизитами: - ВидТО (СправочникСсылка. ВидыТО); - ВидГСМ. . .	Скрипты Tampermonkey для CyberForum, ChatGPT, Claude и пр. Jin X 06.09.2026 Скрипты Tampermonkey для CyberForum, ChatGPT, Claude и пр. Работая с форумом и нейросетями в браузере часто хочется что-то подкорректировать или добавить какого-то функционала. Ниже прикреплён. . .	Программа опроса у.з. расходомера SLS-720F Argus19 02.09.2026 Программа опроса у. з. расходомера SLS-720F Программа опрашивает один раз в минуту три ультразвуковых расходомера SLS-720F через интерфейс RS-485 по протоколу Modbus RTU. Опрашиваются регистры. . .

@Gluck99 408 / 242 / 88 Регистрация: 28.04.2022 Сообщений: 1,207
	29.11.2023, 22:49
	Именч, Cash. 0

Оптимизация pagination

Решение