title: MySQL: Can I do a left join and pull only one row from the join table?
body: <p>I wrote a custom help desk for work and it's been running great... until recently. One query has <em>really</em> slowed down. It takes about 14 seconds now! Here are the relevant tables:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>When a user submits a ticket, it goes into the "tickets" table. Then, as the agents work through the problem, they record the actions they took. Each entry goes into the "solutions" table. In other words, tickets have many solutions.</p>
  
  <p>The goal of the query that has slowed down is to pull all the fields from the "tickets" table and also the latest entry from the "solutions" table. This is the query I've been using:</p>
  
  <pre><code>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 &gt;= '2012-06-20 00:00:00'
  ORDER BY tickets.id DESC
  </code></pre>
  
  <p>Here is an example of what the "latest_solution_entry" field looks like:</p>
  
  <pre><code>6/20/12 * 1337 * 1: I restarted the computer and that fixed the problem. Yes, I took an hour to do this.
  </code></pre>
  
  <p>In PHP, I split up the "latest_solution_entry" field and format it correctly.</p>
  
  <p>When I noticed that the page that runs the query had slowed <em>way</em> down, I ran the query without the subquery and it was super fast. I then ran an <code>EXPLAIN</code> on the original query and got this:</p>
  
  <pre><code>+----+--------------------+-----------+-------+---------------+-----------+---------+---------------------+-------+-----------------------------+
  | 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 |
  +----+--------------------+-----------+-------+---------------+-----------+---------+---------------------+-------+-----------------------------+
  </code></pre>
  
  <p>So I'm looking for a way to make my query more efficient, yet still achieve the same goal. Any ideas?</p>