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

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

nerezus

Вселенский отказник
Оптимизация join

ОС: винда. MySQL настроен под девелопера при установке. Комп средний(Athlon x2 3800+)

Структура:
items: id(праймари индекс), category(индекс), name
images: id(праймари индекс), item_id(индекс), url

Записей 40k и 80k соответственно.

Задача: получить список items в заданной категории с картинками.
При этом нужна всего 1 картинка. Если картинки нет - все равно отображать такой item.

[SQL]SELECT * FROM items
LEFT JOIN images ON images.item_id = items.id
WHERE category = 12
GROUP BY images.item_id
LIMIT 10
[/SQL]

Запрос выполняется 11(!!!) секунд. Повторно полсекунды(кеш, как понимаю).
Как можно оптимизировать?
Есть глупая идея - дергать айтемы, а вторым запросом картинки по WHERE id IN(?), но выглядит ужасно и не хотелось бы юзать.

Заранее стыдно за такой вопрос - будете в дату реги тыкать?) Но такая проблема впервые возникла )
 
akd

akd

dive now, work later
Команда форума
:)
ну ... "из уважения к дате регистрации".
explain.
optimize.
repair.
 

Fortop

Новичок
Код: 
mysql> explain SELECT *  FROM items LEFT JOIN images ON images.item_id = items.id WHERE category = 12 GROUP BY images.item_id LIMIT 10;
+----+-------------+--------+------+---------------+-------+---------+---------------+------+---------------------------------+
| id | select_type | table  | type | possible_keys | key   | key_len | ref           | rows | Extra                           |
+----+-------------+--------+------+---------------+-------+---------+---------------+------+---------------------------------+
|  1 | SIMPLE      | items  | ref  | cat           | cat   | 4       | const         | 4902 | Using temporary; Using filesort |
|  1 | SIMPLE      | images | ref  | items         | items | 4       | test.items.id |    1 |                                 |
+----+-------------+--------+------+---------------+-------+---------+---------------+------+---------------------------------+
2 rows in set (0.00 sec)
Код: 
11 | 0.05028700 | SELECT * FROM items LEFT JOIN images ON images.item_id = items.id WHERE category = 12 GROUP BY images.item_id LIMIT 10
это 40к и 80к соответственно.

А это
| 21 | 0.18702100 | SELECT * FROM items LEFT JOIN images ON images.item_id = items.id WHERE category = 4 GROUP BY images.item_id LIMIT 10 |
| 22 | 0.23667400 | SELECT * FROM items LEFT JOIN images ON images.item_id = items.id WHERE category = 12 GROUP BY images.item_id LIMIT 10 |

200к и 400к

В чем вопрос то?
 

nerezus

Вселенский отказник
Fortop Эм, у вас мой запрос не тормозит? о_О


Про explain то я знаю - думаю, как исправить.
 
zerkms

zerkms

TDD infected
Команда форума
nerezus
если ты хочешь думать сам и не хочешь давать подробностей - то какой смысл в создании темы? чтобы мы тебя пожалели?
 

nerezus

Вселенский отказник
А, так это был призыв выложить логи - я не понял просто )
На самом деле я хочу и люблю думать сам, но тут проблемка такая: решить задачу могу, но решение некрасивое, хочется на SQL.

Explain:
Код: 
EXPLAIN
SELECT * FROM items
LEFT JOIN images ON images.item_id = items.id
WHERE category = 337
GROUP BY images.item_id
LIMIT 10

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	items	ref	category	category	5	const	2344	Using where; Using temporary; Using filesort
1	SIMPLE	images      	ref	item_id	item_id	5	goods.items.id	2	""
repair: Msg_text Ok
optimize: Msg_text Ok

Код: 
CREATE TABLE `items` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `name` varchar(64) default NULL,
  `category` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `category` (`category`)
) ENGINE=MyISAM AUTO_INCREMENT=333512 DEFAULT CHARSET=utf8

CREATE TABLE `images` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `item_id` int(11) default NULL,
  `url` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `item_id` (`item_id`)
) ENGINE=MyISAM AUTO_INCREMENT=75142 DEFAULT CHARSET=utf8
 

Fortop

Новичок
Проблема вобщем-то не в SQL

Смотрите настройки и логи сервера.
 

nerezus

Вселенский отказник
> Проблема вобщем-то не в SQL
Печально (
Я надеялся, что мой косяк, а оказывается, это mysql так расстраивает (
 
Wicked

Wicked

Новичок
у меня такие варианты:

1)
создай индекс index (category, id)
поменяй в запросе group by на: group by items.category, items.id
и explain в студию

2) может помочь join с derived таблицей нужных десяти записей из items
типа:
from items
inner join (select id from items where category = 12 limit 10) as d on (d.id = items.id)
left join images ...

3) добавить images'ам флаг "это главная картинка" и джоинить с условием on (images.item_id = items.id and images.is_main) - тогда группировка вообще не потребуется

4)
Есть глупая идея - дергать айтемы, а вторым запросом картинки по WHERE id IN(?), но выглядит ужасно и не хотелось бы юзать.
Нажмите для раскрытия...
это совершенно не глупо :) лично для меня это одна из самых вкусных и правильных фишек доктрины - loadRelated() :)

-~{}~ 11.03.10 11:34:

к "4)": http://softsearch.ru/i/download/mmug.pdf

-~{}~ 11.03.10 11:39:

кстати, мне одному комбинация
LEFT JOIN images
и
GROUP BY images.item_id
кажется фееричной? :)

-~{}~ 11.03.10 12:44:

и причем тут битые индексы? я, пытаясь поставить себя на место оптимизатора, ну никак не могу придумать нормальный план выполнения того запроса... так что радуйся, nerezus, это действительно твой косяк :)

-~{}~ 13.03.10 12:23:

мне все же интересно, помогло ли что-нибудь из этого .-)
 

nerezus

Вселенский отказник
> nerezus, проблема в битых индексах скорее всего
Нене, я проверил )

1) Одинаково.
4) Работает, но я про него говорил )
3) Некрасивое решение(точнее не универсальное), хоть для этой задачи и пойдет. Но у меня то цель - не задачу решить.
2) Я удивлен. По сути это пункт 4, но 1 запросом(трехтабличным).
Я про него сразу подумал, но отбросил, думал что добавление подзапроса сразу его стормозит. Так нет же, он всего лишь немного медленнее 4ого решения )
Спасибо за подсказки)

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

P.S. А вот то, что MySQL не умеет даже массивов - крайне хреново. С ними задача была бы элементарной:
SELECT id, name, category, images[1] FROM items;
 
zerkms

zerkms

TDD infected
Команда форума
да уж, элементарной, до тех пор, пока тебя устраивает порядок (кстати, неизвестно какой - хронологический?) в "массиве" images.
 
Wicked

Wicked

Новичок
1) Одинаково.
Нажмите для раскрытия...
одинаково чем? с "Using where; Using temporary; Using filesort"?

-~{}~ 15.03.10 13:06:

А я не вижу причин не оптимальности: все необходимые индексы, по которым ведется поиск, есть для обоих запросов.
Нажмите для раскрытия...
вопрос в том, может ли их использовать mysql :)
например, если джоин начинается с items (а с left join по-другому, наверное, и не бывает), то group by images.item_id ну никак не придумает, как можно воспользоваться индексами...

-~{}~ 15.03.10 13:14:

вижу, что group by items.category, items.id был лишним, это оптимизатор сам разрулил:

но двойной индекс по (category, id) тут необходим.

на примере моих похожих таблиц и данных:
KEY `priority` (`priority`),
KEY `priority_2` (`priority`,`id`)

Код: 
mysql> EXPLAIN SELECT * FROM image_processing_task AS t IGNORE INDEX (priority_2) LEFT JOIN image_processing_task_dep AS d ON t.id = d.first_task_id WHERE t.priority = 10 GROUP BY t.id LIMIT 10;
+----+-------------+-------+------+---------------+---------------+---------+------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key           | key_len | ref              | rows | Extra                                        |
+----+-------------+-------+------+---------------+---------------+---------+------------------+------+----------------------------------------------+
|  1 | SIMPLE      | t     | ref  | priority      | priority      | 5       | const            | 3068 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | d     | ref  | first_task_id | first_task_id | 4       | makeitstuff.t.id |    1 |                                              | 
+----+-------------+-------+------+---------------+---------------+---------+------------------+------+----------------------------------------------+

mysql> EXPLAIN SELECT * FROM image_processing_task AS t                           LEFT JOIN image_processing_task_dep AS d ON t.id = d.first_task_id WHERE t.priority = 10 GROUP BY t.id LIMIT 10;
+----+-------------+-------+------+---------------------+---------------+---------+------------------+------+-------------+
| id | select_type | table | type | possible_keys       | key           | key_len | ref              | rows | Extra       |
+----+-------------+-------+------+---------------------+---------------+---------+------------------+------+-------------+
|  1 | SIMPLE      | t     | ref  | priority,priority_2 | priority_2    | 5       | const            | 3068 | Using where | 
|  1 | SIMPLE      | d     | ref  | first_task_id       | first_task_id | 4       | makeitstuff.t.id |    1 |             | 
+----+-------------+-------+------+---------------------+---------------+---------+------------------+------+-------------+
 

nerezus

Вселенский отказник
одинаково чем? с "Using where; Using temporary; Using filesort"?
Время выполнения запроса одинаково.

но двойной индекс по (category, id) тут необходим.
Нажмите для раскрытия...
Проверил - время такое же.
И разве при наличии индексов на каждом поле, но без общего индекса падет производительность? мускул не поймет?

P.S. Я тут заметил... хелловорлд на ZF у меня > 1с, а индекс форума SMF грузится пару секунд) Эх, с хорошей зарплаты куплю себе новенький 4ядерник, мой 3800+ х2 уже тормоз (
Нажмите для раскрытия...
Нажмите для раскрытия...
 

totamon

Новичок
не пойму, при поставленой задаче в чем смысл GROUP BY images.item_id? без нее на один id.item буде несколько строк со всеми фото?
 
Войдите или зарегистрируйтесь для ответа.
Сверху