MySQL: Могу ли я выполнить левое соединение и извлечь только одну строку из таблицы соединений?

Я написал специальную справочную службу для работы, и она работает отлично ... до недавнего времени. Один запрос имеет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)

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

оно должно выполнять выбор для каждой отдельной строки. Я считаю, что лучше в таких случаях помещать встроенное представление в предложение 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 

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

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

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

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

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))
 21 июн. 2012 г., 11:56
Очень хороший трюк, спасибо!
 28 февр. 2015 г., 20:05
Ты мой герой! Это очень хороший трюк.
 Nick22 июн. 2012 г., 05:14
Хорошо понял. Я просто изменилINNER JOIN кLEFT JOIN, Я все еще проверяю некоторые вещи, но сообщу.
 Nick22 июн. 2012 г., 04:50
Так близко! Спасибо за вашу помощь! Последнее, что мне нужно, чтобы билеты без решений все еще появлялись. Это просто, чтоlatest_solution_entry поле должно быть пустым в этом случае.
 Nick22 июн. 2012 г., 05:28
Как ни странно, исходный запрос занимает всего около 0,2136 секунд для выполнения сегодня (в отличие от 14 секунд). Я попробовал его с новым (вашим) запросом, и он занимает около 0,0026 секунд, что составляет около 1,2% времени исходного запроса!Much лучше! Я не уверен, что вызвало вчера 14 секунд, но, надеюсь, с этим новым запросом мы сейчас будем в гораздо лучшей форме. Спасибо вам большое!

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

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

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

Ваш ответ на вопрос