Меню
Что нового?

Mysql Оптимизировать запрос, в таблице 6млн записей

lolka02

Новичок
Помогите Оптимизировать запрос, в таблице 6млн записей и каждый день увеличивается на 20к, индексы добавлены, дальше не знаю в какую сторону смотреть. Вот запрос, иногда при нагрузке часто вот такой запрос выполняется по 30-100 секунд (это запрос для выполки похожих записей в каждой карточке товара)

Код: 
SELECT distinct(product.id), product.id,
               product.name,
               product.descriptio
               product.city_id,
               product.price,
               product.company_name,
               product.timestamp_update,
               product.company_id,
               company.company_type,
               company.image as company_image,
               city.name as city_name
FROM `product_to_tag` `v2t`
JOIN `product` ON product.id = v2t.product_id
JOIN `product_to_city` `vtc` ON product.id = vtc.product_id
JOIN `city` `c` ON c.id = vtc.city_id
LEFT JOIN `city` `city` ON city.id=product.city_id
LEFT JOIN `company` ON company.id=product.company_id
WHERE ((`product`.`publish` = 1)) AND (product.id !=  5016460) AND (c.id = 99 or c.parent_id = 99) AND ((`v2t`.`tag_id` IN (65, 181, 228, 1135)))
ORDER BY `product`.`timestamp_update` DESC LIMIT 30;
 
Yoskaldyr

Yoskaldyr

"Спамер"
Партнер клуба
@lolka02, Нужна схема таблицы, и общее количество записей в таблице. т.к. главное не просто добавить индексы а правильно добавить.
Также зависит от того какая версия Mysql/Percona/Maria стоят. В зависимости от версии у них магут быть оптимизации подобных запросов (там где есть OR: c.id = 99 or c.parent_id = 99)
 
Yoskaldyr

Yoskaldyr

"Спамер"
Партнер клуба
@lolka02, Желательно хотя бы 5.6 или еще новее. Начиная с этих версий улучшипась оптимизация объединения индексов (Index Merge Optimization). Иногда может помочь с подобными запросами (условие или в середине).
Но самое важное это количество записей в таблице + схема таблицы
 

lolka02

Новичок
как варинат можно ли заранее добавить в отдельную таблицу все похожие товары для каждого товара? этот запрос делает выборку похожих товаров...
 
WMix

WMix

герр M:)ller
Партнер клуба
фигаж се труд совершил!,
а че, консолькой работать не умеешь?
PHP: 
$ mysql -u root -p world
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| City            |
| Country         |
| CountryLanguage |
| cities          |
| person          |
| persons         |
+-----------------+
6 rows in set (0,00 sec)

mysql> show columns from City;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0,00 sec)

mysql> show index from City;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| City  |          0 | PRIMARY     |            1 | ID          | A         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
| City  |          1 | CountryCode |            1 | CountryCode | A         |         232 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0,00 sec)

mysql> explain select Country.Code, count(distinct City.ID) from Country left join City on City.CountryCode = Country.Code
    ->  group by Country.Code;
+----+-------------+---------+------------+-------+---------------+-------------+---------+--------------------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key         | key_len | ref                | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+-------------+---------+--------------------+------+----------+-------------+
|  1 | SIMPLE      | Country | NULL       | index | PRIMARY       | PRIMARY     | 3       | NULL               |  239 |   100.00 | Using index |
|  1 | SIMPLE      | City    | NULL       | ref   | CountryCode   | CountryCode | 3       | world.Country.Code |   18 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-------------+---------+--------------------+------+----------+-------------+
2 rows in set, 1 warning (0,00 sec)

mysql>
 
antson

antson

Новичок
Партнер клуба
v2t как понимаю самая большая. Если есть возможность (на сервере достаточно оперативки) поиграйся буферами муськи для индексов . так чтобы пропало using temporary , using filesort

как вариант протестируй связку из 2 запрсов
1) выбирает только ид товаров
если бяка пропадет из плана, то
2) select * from products where id in (идишники из первого)
 
Последнее редактирование:

lolka02

Новичок
antson написал(а):
v2t как понимаю самая большая. Если есть возможность (на сервере достаточно оперативки) поиграйся буферами муськи для индексов . так чтобы пропало using temporary , using filesort
Нажмите для раскрытия...
Можете рассказать как именно делать? в конфигах Mysql ?
antson написал(а):
как вариант протестируй связку из 2 запрсов
1) выбирает только ид товаров
если бяка пропадет из плана, то
2) select * from products where id in (идишники из первого)
Нажмите для раскрытия...
тут просто тогда сортировка не очень будет, например нам нужен только 15 товаров, придется выбирать тогда все товары и во втором запросе только сортировку и ограничение ставить, или же опять надо будет выбрать только опубликованные товары, а для этого придется делать джоин и опять придем к главному запросу
 
antson

antson

Новичок
Партнер клуба
@lolka02, начнем с того где хоститься ?
шаред - нельзя настраивать.
vps,dedicated - смотрим сколько памяти.
смотрим текущий my.cnf
находим рекомендуемый под заданный объем озу и тип inodb или isam
 

lolka02

Новичок
@antson, hetzner, dedicated. 64гб памяти. тип innodb.
Только что заметил что у меня два одинаковых индекса для поля publish от этого тоже тормозит наверное?
 

lolka02

Новичок
+ вот из за этого условия AND (c.id = 99 or c.parent_id = 99) запрос выполняется дольше когда убираю разница 12-20 секунд получается
 
Yoskaldyr

Yoskaldyr

"Спамер"
Партнер клуба
Во первых настройки базы - чтобы не вникать детально можно скриптами типа MySQLTuner или MySQL Tuning Primer Script.

Как вариант может тупить из-за сортировки, для проверки выкинуть из запроса ORDER BY `product`.`timestamp_update` DESC и посмотреть что получится.
Но все это предположения, без explain-а точно все-равно не сказать.

P.S. Хотя мне кажется в данном случае вряд-ли какие советы помогут. Т.к. надо и сильно запрос переписать и настроить базу, а это скилл значительно выше скилла топикстартера, который довольно однозначный судя по запросу бессмысленному и беспощадному и его любви к phpmysqladmin-у.
 
Yoskaldyr

Yoskaldyr

"Спамер"
Партнер клуба
Хотя может топик стартеру просто досталось такое тяжелое наследство, а не он сам это писал. Тогда можно только посочувствовать. Архитектура базы, таблиц и связей явно г-но.
 

lolka02

Новичок
@Yoskaldyr, да проектировал базу не я, убрал order в результате в explain пропали using temporary , using filesort. Но мне без сортировки никак(( нужно именно чтобы последние товары были вначале
 
Войдите или зарегистрируйтесь для ответа.
Сверху