Оптимизация вложенного запроса.

[A-Lex[FM]]

Оптимизация вложенного запроса.

Всем привет.
Вчера появился трабл со временем выполнения вложенного запроса.
имеем:
1. Таблица категорий ([int] id, [varchar] title)
2. Таблица данных ([int] id, [int] section_id, [varchar] title, [text] article)
(естественно id и category_id - индексные)

задача:
Выбрать последние записи в каждой категории. Структуру таблиц не менять.

моё решение:
[sql]
SELECT s.id, s.title AS section_title, g. *
FROM sections s
LEFT JOIN gdata g ON s.id = g.section_id
WHERE g.id
IN (
SELECT MAX( g1.id )
FROM sections s1
LEFT JOIN gdata g1 ON g1.section_id = s1.id
GROUP BY s1.id
)
ORDER BY s.id
[/sql]

появившаяся проблема:
запрос отрабатывает очень долго, после изменения данных в таблицах ~60 сек. После кэширования ~6 сек.

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

 

[zerkms]

для начала - показать EXPLAIN и структуры таблиц

 

[A-Lex[FM]]

1. Таблица категорий ([int] id, [varchar] title)
2. Таблица данных ([int] id, [int] section_id, [varchar] title, [text] article)
(естественно id и category_id - индексные)

EXPLAIN могу паказать только завтра.

К примеру:
sections
id | title
--------------
1 | Общее
2 | Разное
3 | Новое

gdata
id | section_id | title | article
------------------------------
1 | 1 | Проверка 1 | ...
2 | 3 | test | ...
3 | 1 | test2 | ...
4 | 2 | test3 | ...

Так вот на выходе нужно получить

section_id | section_title | id | title | article
-----------------------------------------
1 | Общее | 3 | test2 | ...
2 | Разное | 4 | test3 | ...
3 | Новое | 2 | test| ...

 

[aixrus]

вообще id IN ( SELECT

не очень хорошая конструкция
не знаю как в mysql это реализуется, но при такой записи в некоторых других БД этот вложенный запрос будет исполняться для каждой строчки

тут два варианта
1. через псевданимы таблицы (если mysql это умеет делать)
SELECT * FROM table1, (SELECT * FROM table2) as table 2
а потом делать объединение по ключам table1 и table2

2. в скрипте делать выборку условий, формировать список занчений 'id1','id2','id3' итд

после чего вставльть и выполнять основной запрос с IN (полученное условие)


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

 

[walrus]

добавлю от себя:
1. мускул, если не ошибаюсь, не работает с такими вот структурами, какие предлагает aixrus - SELECT * FROM table1, (SELECT * FROM table2) as table 2

2. to A-Lex[FM] а где explain?
не всегда, когда используется конструкция IN, мускул пользуется индексами, гораздо быстрее в таких случаях работают запросы с UNION, в каждом из которых есть условие id = value

3. а зачем во вложенном запросе делается объединение, если оно есть в основном? можно еще избавиться от LEFT JOIN

 

[A-Lex[FM]]

Исправил запрос на такой
[sql]
SELECT s.id, s.title AS section_title, g. *
FROM sections s
LEFT JOIN gdata g ON s.id = g.section_id
WHERE g.id
IN (
SELECT MAX( g1.id )
FROM gdata g1
GROUP BY g1.section_id
)
ORDER BY s.id
[/sql]

время выполнения сократилось в 10 раз.
попробую ещё поиграться с кэшированием.
всем спасибо.

 

[denver]

A-Lex[FM]

попробую ещё поиграться с кэшированием.

Почитай лучше про EXPLAIN, как минимум чтобы понять с чего именно стало лучше в 10 раз.

 

[walrus]

а можно еще и navicat поставить и посмотреть время выполнения каждого запроса, к тому же там explain автоматически для каждого запроса делается

 

[camka]

A-Lex[FM]
Попробуй вставить подзапрос во временную таблицу с соответствующими индексами и потом просто сделать с ней JOIN.

Были случаи, когда, если подзапрос включал ту же таблицу, что и внешний запрос, выборка происходила исключительно медленно. Временная таблица исправляла результат кардинально.

 

[A-Lex[FM]]

спасибо за советы.
вечером буду ставить экспирименты.

Да. может кто-нибудь посоветует ещё, мне надо делать полнотекстовый поиск одновременно по 3 и более таблицам, сейчас я объединяю через UNION и сортирую по релевантности, но это не очень удобно.
Сделал вьюшку из этих таблиц, но там нельзя юзать индексы, что не позитивно. Можно ли как-нибудь всё-таки их заюзать.

 

[walrus]

С вьюшками прокатит только в 5 мускуле, в старых версиях их нет.
а разве индексы не передаюся с основных таблиц?

 

[A-Lex[FM]]

fulltext похоже что нет

 

[chira]

A-Lex[FM]
возможно маленькое дополнение тебе тоже должно помочь:

SELECT s.id, s.title AS section_title, g. *
FROM sections s
LEFT JOIN gdata g ON s.id = g.section_id
WHERE g.id
IN (
  SELECT MAX( g1.id )
  FROM gdata g1
  [b]WHERE s.id = g1.section_id[/b]
--  GROUP BY g1.section_id -- тогда можно без GROUP BY
)
ORDER BY s.id

 

[A-Lex[FM]]

chira, спасибо огромное, колличество обрабатываемых строк уменьшилось ещё в 8 раз.
По EXPLAIN получается следующее:
1 вариант: обрабатывалось 4 подзапроса, учавствовало 8*105*8*105 строк
2 вариант: 3 подзапроса, 8*105*105, не использовались индекы
3 и последний вариант: 3 подзапроса, 8*105*105, использование индексов.

 

[chira]

A-Lex[FM]

последний штрих, не обязательный и его нужно обдумать ...
составной индекс на таблице gdata (section_id,id), это позволит читать данные только из индекса и не трогать саму таблицу в подзапросе ...

 

[A-Lex[FM]]

ещё раз респект
в принципе составной индекс может помочь, вечером попробую на фэйковой базе.