Вопрос по query-optimization, mysql – MySQL: Могу ли я выполнить левое соединение и извлечь только одну строку из таблицы соединений?

4

Я написал специальную справочную службу для работы, и она работает отлично ... до недавнего времени. Один запрос имеетreally замедлился. Это занимает около 14 секунд сейчас! Вот соответствующие таблицы:

CREATE TABLE `tickets` (
  `id` int(11) unsigned NOT NULL DEFAULT '0',
  `date_submitted` datetime DEFAULT NULL,
  `date_closed` datetime DEFAULT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `description` text,
  `agent_id` smallint(5) unsigned NOT NULL DEFAULT '1',
  `status` smallint(5) unsigned NOT NULL DEFAULT '1',
  `priority` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `date_closed` (`date_closed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `solutions` (
  `id` int(10) unsigned NOT NULL,
  `ticket_id` mediumint(8) unsigned DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `hours_spent` float DEFAULT NULL,
  `agent_id` smallint(5) unsigned DEFAULT NULL,
  `body` text,
  PRIMARY KEY (`id`),
  KEY `ticket_id` (`ticket_id`),
  KEY `date` (`date`),
  KEY `hours_spent` (`hours_spent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Когда пользователь отправляет билет, он попадает в раздел «Билеты». Таблица. Затем, когда агенты прорабатывают проблему, они записывают действия, которые они предприняли. Каждая запись входит в раздел «Решения». Таблица. Другими словами, у билетов есть много решений.

Цель замедленного запроса - извлечь все поля из & quot; tickets & quot; таблица, а также последняя запись из «Решения» Таблица. Это запрос, который я использовал:

SELECT tickets.*,
    (SELECT CONCAT_WS(" * ", DATE_FORMAT(solutions.date, "%c/%e/%y"), solutions.hours_spent, CONCAT_WS(": ", solutions.agent_id, solutions.body))
    FROM solutions
    WHERE solutions.ticket_id = tickets.id
    ORDER BY solutions.date DESC, solutions.id DESC
    LIMIT 1
) AS latest_solution_entry
FROM tickets
WHERE tickets.date_closed IS NULL
OR tickets.date_closed >= '2012-06-20 00:00:00'
ORDER BY tickets.id DESC

Вот пример того, что "latest_solution_entry" поле выглядит так:

6/20/12 * 1337 * 1: I restarted the computer and that fixed the problem. Yes, I took an hour to do this.

В PHP я разделил & lt; latest_solution_entry & quot; поле и отформатируйте его правильно.

Когда я заметил, что страница, на которой выполняется запрос, замедлиласьway вниз, я выполнил запрос без подзапроса, и это было очень быстро. Я тогда побежалEXPLAIN на исходный запрос и получил это:

+----+--------------------+-----------+-------+---------------+-----------+---------+---------------------+-------+-----------------------------+
| id | select_type        | table     | type  | possible_keys | key       | key_len | ref                 | rows  | Extra                       |
+----+--------------------+-----------+-------+---------------+-----------+---------+---------------------+-------+-----------------------------+
|  1 | PRIMARY            | tickets   | index | date_closed   | PRIMARY   | 4       | NULL                | 35804 | Using where                 |
|  2 | DEPENDENT SUBQUERY | solutions | ref   | ticket_id     | ticket_id | 4       | helpdesk.tickets.id |     1 | Using where; Using filesort |
+----+--------------------+-----------+-------+---------------+-----------+---------+---------------------+-------+-----------------------------+

Поэтому я ищу способ сделать мой запрос более эффективным, но при этом достичь той же цели. Есть идеи?

Ваш Ответ

4   ответа
1

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

SELECT t.*, 
       Concat_ws(" * ", Date_format(s.date, "%c/%e/%y"), s.hours_spent, 
       Concat_ws(":", s.agent_id, s.body)) 
FROM   tickets t 
       INNER JOIN (SELECT solutions.ticket_id,
                          Max(solutions.date) maxdate 
                   FROM   solutions 
                   GROUP  BY solutions.ticket_id) last_solutions 
               ON t.id = last_solutions.ticket_id
       INNER JOIN (SELECT solutions.ticket_id,
                          solutions.date,
                          Max(solutions.id) maxid 
                   FROM   solutions 
                   GROUP  BY solutions.ticket_id,
                            solutions.date) last_solution
              ON last_solutions.ticket_id = last_solution.ticket_id 
                 and last_solutions.maxDate = last_solution.Date
       INNER JOIN solutions s 
               ON last_solution.maxid = s.id
WHERE  t.date_closed IS NULL 
        OR t.date_closed >= '2012-06-20 00:00:00' 
ORDER  BY t.id DESC 

Примечание: вам может понадобиться сделать это ЛЕВЫМ объединением в зависимости от ваших потребностей

Спасибо! Я попробовал этот запрос дважды в phpMyAdmin, и мой браузер перестал отвечать на запросы оба раза. Я побежалEXPLAIN на это, и похоже, что он делает много. Чтобы дать вам представление о том, с чем я работаю, «билеты» таблица содержит около 32 тыс. строк и «решения»; Таблица имеет около 40 тыс. строк. Есть ли что-то, что я делаю неправильно? Я очень ценю вашу помощь! Nick
хм, это не много строк. Вполне возможно, что этот запрос хуже из-за двойных агрегатов. Что-то, что могло бы помочь, было бы, если бы у solutions.date был индекс, а также solutions.id и solutions.ticket. Это может помочь вашему исходному запросу. После этого у меня нет идей, извините.
Я исправил первыйON который был сломан и использовал два подзапроса, чтобы получить первый на дату, а затем на ID
Это близко к тому, что мне нужно. Спасибо! я думаюON t.id = last_solution.maxid неправильно, хотя, потому чтоtickets.id коррелирует сsolutions.ticket_idнеsolutions.id, Другая проблема заключается в том, чтоORDER BY solutions.date DESC, solutions.id DESC Исходя из моего исходного запроса довольно критично. Агенты могут изменять дату, когда они представили свое решение, чтобы ониcould установить для всех их решений одну и ту же дату / время или дату в прошлом и т. д. Поэтому я хочу сначала отключиться от самой последней даты,then самый высокий идентификатор. Не только самый высокий ID. Это все еще возможно? Спасибо! Nick
17

вы хотели бы выбрать каждый билет и его последнее решение.

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

SELECT
  t.*,
  s1.*
FROM tickets t
INNER JOIN solutions s1 ON t.id = s1.ticket_id
LEFT JOIN solutions s2 ON s1.ticket_id = s2.ticket_id AND s2.id > s1.id
WHERE s2.id IS NULL;

Я написал только сердце шаблона для лучшего понимания.

Ключи:

the LEFT JOIN of the solutions table with itself with the s1.ticket_id = s2.ticket_id condition: it emulates the GROUP BY ticket_id.

the condition s2.id > s1.id : it is the SQL for "I only want the last solution", it emulates the MAX(). I assumed that in your model, the last means with the greatest id but you could use here a condition on the date. Note that s2.id < s1.id would give you the first solution.

the WHERE clause s2.id IS NULL: the weirdest one but absolutely necessary... keeps only the records you want.

Попробуйте и дайте мне знать :)

Edit 1: Я только что понял, что второе предположение слишком упрощает проблему. Это делает его еще более интересным: я пытаюсь понять, как этот шаблон может работать с вашимdate, id упорядоченность.

Edit 2: Хорошо, это прекрасно работает с небольшим поворотом. Условие на левое соединение становится:

LEFT JOIN solutions s2 ON s1.ticket_id = s2.ticket_id
  AND (s2.date > s1.date OR (s2.date = s1.date AND s2.id > s1.id))
Хорошо понял. Я просто изменилINNER JOIN кLEFT JOIN, Я все еще проверяю некоторые вещи, но сообщу. Nick
Ты мой герой! Это очень хороший трюк.
Очень хороший трюк, спасибо!
Так близко! Спасибо за вашу помощь! Последнее, что мне нужно, чтобы билеты без решений все еще появлялись. Это просто, чтоlatest_solution_entry поле должно быть пустым в этом случае. Nick
Как ни странно, исходный запрос занимает всего около 0,2136 секунд для выполнения сегодня (в отличие от 14 секунд). Я попробовал его с новым (вашим) запросом, и он занимает около 0,0026 секунд, что составляет около 1,2% времени исходного запроса!Much лучше! Я не уверен, что вызвало вчера 14 секунд, но, надеюсь, с этим новым запросом мы сейчас будем в гораздо лучшей форме. Спасибо вам большое! Nick
1

SELECT *
FROM (
  -- for each ticket get the most recent solution date
  SELECT ticket_id, MAX(solutions.date) as date
  FROM solutions
  GROUP BY ticket_id
) t
JOIN tickets ON t.ticket_id = tickets.id
WHERE tickets.date_closed IS NULL OR tickets.date_closed >= '2012-06-20 00:00:00'
ORDER BY tickets.id DESC

Обратите внимание, что при наличии заявки с двумя решениями на одну и ту же дату в вашем наборе результатов будут дубликаты записей. Вам понадобится другое соединение, чтобы удалить эти дубликаты или использовать абсолютную последовательность, такую как последовательный (увеличивающий первичный ключ).

Это не сработает, правильно. Когда вы не группируете по полю в Mysql (в данном случае в качестве даты), MySQL возвращает первое значение в группе. Такunless the first value is also the max это будет отфильтровано. В этомsimple demonstration возвращается только одна запись, когда должно быть две
Вы правы. На самом деле, я считаю, что SQL, который я дал, был строго нелегальным Я заменил свой ответ.
0

SELECT DISTINCT s1.ticket_id, t.*,  s1.*
FROM tickets t
LEFT JOIN solutions s1 ON t.id = s1.ticket_id

Похожие вопросы