sql.sql

Tickets.sql

# Two SQL queries. One built using ActiveRecord, the other - manually assembled.
# Both return the same result.
# Each table has 500 thousand and a million records respectively (Ticket has many Messages).
# The first query works 10 times slower.
#

# 1) Active Record
# 130 rows retrieved starting from 1 in 645 ms (execution: 635 ms, fetching: 10 ms)
# 130 rows retrieved starting from 1 in 623 ms (execution: 611 ms, fetching: 12 ms)
# 130 rows retrieved starting from 1 in 640 ms (execution: 631 ms, fetching: 9 ms)
SELECT
  tickets.id,
  tickets.title,
  MAX(ticket_messages.date) AS last_answer
FROM
  tickets
INNER JOIN ticket_messages ON tickets.id = ticket_messages.ticket_id
WHERE tickets.automated = 0
GROUP BY tickets.id
ORDER BY updated_at DESC
LIMIT 130;


#2) SQL
# 130 rows retrieved starting from 1 in 96 ms (execution: 72 ms, fetching: 24 ms)
# 130 rows retrieved starting from 1 in 85 ms (execution: 70 ms, fetching: 15 ms)
# 130 rows retrieved starting from 1 in 87 ms (execution: 77 ms, fetching: 10 ms)
SELECT
  tickets.id,
  tickets.title,
  MAX(ticket_messages.date) AS last_answer
FROM
  (SELECT * FROM tickets WHERE tickets.automated = 0 ORDER BY updated_at DESC LIMIT 130) tickets
INNER JOIN ticket_messages ON tickets.id = ticket_messages.ticket_id
GROUP BY tickets.id
ORDER BY updated_at DESC;