title: mysql - join first and last records by group type?
body: <p>With a table like:</p>
  
  <pre><code>idx type dat
  0   a    foo1
  1   b    foo2
  2   c    foo3
  3   a    foo4
  4   b    foo5
  5   c    foo6
  6   a    foo7
  7   b    foo8
  8   c    foo9
  </code></pre>
  
  <p>How can i get the first and last data of each type:</p>
  
  <p>for example:</p>
  
  <pre><code>a foo1 foo7
  b foo2 foo8
  c foo3 foo9
  </code></pre>
  
  <p>I have tried this query, but its way to slow, even with indexes on idx and type:</p>
  
  <pre><code>select mins.type, mins.dat, mytable.dat from mytable
  
  --get the maximums
  inner join
   (select max(idx) as maxidx from mytable group by type) as a
  on a.maxidx = mytable.idx
  
  
  --join the maximums to the minimums
  inner join
  
  --get the minimums
   (select * from mytable inner join (select min(idx) as minidx from mytable group by type) as b 
  on b.minidx = mytable.idx ) as mins
  
  on issues.type = mins.type
  </code></pre>
  
  <p>The join of on issues.type = mins.type seems to be what is slowing it down so much because mins.type is derived and not indexed?</p>