We are developing a ticket system and for the dashboard we want to show the tickets with it's latest status. We have two tables. The first one for the ticket itself and a second table for the individual edits.
The system is running already, but the performance for the dashboard is very bad (6 seconds for ~1300 tickets). At first we used a statemant which selected 'where timestamp = (select max(Timestamp))' for every ticket. In the second step we created a view which only includes the latest timestamp for every ticket, but we are not able to also include the correct status into this view.
So the main Problem might be, that we can't build a table in which for every ticket the lastest ins_date and also the latest status is selected.
Simplyfied database looks like:
CREATE TABLE `ticket` (
`id` int(10) NOT NULL,
`betreff` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `ticket_relation` (
`id` int(11) NOT NULL,
`ticket` int(10) NOT NULL,
`info` varchar(10000) DEFAULT NULL,
`status` int(1) NOT NULL DEFAULT '0',
`ins_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`ins_user` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `ticket` (`id`, `betreff`) VALUES
(1, 'Technische Frage'),
(2, 'Ticket 2'),
(3, 'Weitere Fragen');
INSERT INTO `ticket_relation` (`id`, `ticket`, `info`, `status`, `ins_date`, `ins_user`) VALUES
(1, 1, 'Betreff 1', 0, '2019-05-28 11:02:18', 123),
(2, 1, 'Betreff 2', 3, '2019-05-28 12:07:36', 123),
(3, 2, 'Betreff 3', 0, '2019-05-29 06:49:32', 123),
(4, 3, 'Betreff 4', 1, '2019-05-29 07:44:07', 123),
(5, 2, 'Betreff 5', 1, '2019-05-29 07:49:32', 123),
(6, 2, 'Betreff 6', 3, '2019-05-29 08:49:32', 123),
(7, 3, 'Betreff 7', 2, '2019-05-29 09:49:32', 123),
(8, 2, 'Betreff 8', 1, '2019-05-29 10:49:32', 123),
(9, 3, 'Betreff 9', 2, '2019-05-29 11:49:32', 123),
(10, 3, 'Betreff 10', 3, '2019-05-29 12:49:32', 123);
I have created a SQL Fiddle: http://sqlfiddle.com/#!9/a873b6/3 The first three Statements are attempts that won't work correct or way too slow. The last one is the key I think, but I don't understand, why this gets the status wrong.
The attempt to create the table with latest ins_date AND status for each ticket:
SELECT
ticket, status, MAX(ins_date) as max_date
FROM
ticket_relation
GROUP BY
ticket
ORDER BY
ins_date DESC;
This query gets the correct (latest) ins_date for every ticket, but not the latest status:
+--------+--------+----------------------+
| ticket | status | max_date |
+--------+--------+----------------------+
| 3 | 1 | 2019-05-29T12:49:32Z |
+--------+--------+----------------------+
| 2 | 0 | 2019-05-29T10:49:32Z |
+--------+--------+----------------------+
| 1 | 0 | 2019-05-28T12:07:36Z |
+--------+--------+----------------------+
Expected output would be this:
+--------+--------+----------------------+
| ticket | status | max_date |
+--------+--------+----------------------+
| 3 | 3 | 2019-05-29T12:49:32Z |
+--------+--------+----------------------+
| 2 | 1 | 2019-05-29T10:49:32Z |
+--------+--------+----------------------+
| 1 | 3 | 2019-05-28T12:07:36Z |
+--------+--------+----------------------+
Is there a efficient way to select the latest timestamp and status for every ticket in the tiket-table?
Other approach is to think filtering not GROUPing..
Query
SELECT
ticket_relation_1.ticket
, ticket_relation_1.status
, ticket_relation_1.ins_date
FROM
ticket_relation AS ticket_relation_1
LEFT JOIN
ticket_relation AS ticket_relation_2
ON
ticket_relation_1.ticket = ticket_relation_2.ticket
AND
ticket_relation_1.ins_date < ticket_relation_2.ins_date
WHERE
ticket_relation_2.id IS NULL
ORDER BY
ticket_relation_1.id DESC
Result
| ticket | status | ins_date |
| ------ | ------ | ------------------- |
| 3 | 3 | 2019-05-29 12:49:32 |
| 2 | 1 | 2019-05-29 10:49:32 |
| 1 | 3 | 2019-05-28 12:07:36 |
see demo
This query would require a index KEY(ticket, ins_date, id) to get max performance..