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

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

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

objective замедленного запроса состоит в том, чтобы вытащить все поля из таблицы «билеты», а также последнюю запись из таблицы «решения». Это запрос, который я использовал:

 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 я разделил поле «latest_solution_entry» и отформатировал его правильно.

Когда я заметил, что страница, выполняющая запрос, замедлилась, я выполнил запрос без подзапроса, и это было очень быстро. Затем я запустил 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 | +----+--------------------+-----------+-------+---------------+-----------+---------+---------------------+-------+-----------------------------+ 

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

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

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

     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; 

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

    Ключи:

    • LEFT JOIN таблицы solutions с самим собой с s1.ticket_id = s2.ticket_id : он эмулирует GROUP BY ticket_id .

    • условие s2.id > s1.id : это SQL для «Я хочу только последнее решение», он эмулирует MAX() . Я предположил, что в вашей модели the last означает with the greatest id но вы можете использовать здесь условие на дату. Обратите внимание, что s2.id < s1.id даст вам первое решение.

    • предложение WHERE s2.id IS NULL : самый странный, но абсолютно необходимый ... хранит только нужные вам записи.

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

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

    Edit 2: Хорошо, он отлично работает с небольшим завихрением. Условие в LEFT JOIN становится:

     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)) 

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

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

    Попробуй это:

     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 

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

    в зависимости от цели, я даю идею:

     SELECT DISTINCT s1.ticket_id, t.*, s1.* FROM tickets t LEFT JOIN solutions s1 ON t.id = s1.ticket_id 
    Давайте будем гением компьютера.