title: MySQL versus SQL Server Express Performance Comparison
body: <p>I have a somewhat complex query with roughly 100K rows.</p>
  
  <p>The query runs in 13 seconds in SQL Server Express (run on my dev box)</p>
  
  <p>The same query with the same indexing and tables takes over 15+ minutes to run on MySQL 5.1 (run on my production box - much more powerful and tested with 100% resources)  And sometimes the query crashes the machine with an out of memory error.</p>
  
  <p>What am I doing wrong in MySQL?  Why does it take so long?</p>
  
  <pre><code>select e8.*
  from table_a e8
  inner join (
      select max(e6.id) as id, e6.category, e6.entity, e6.service_date
      from (
          select e4.* 
          from table_a e4
          inner join (
              select max(e2.id) as id, e3.rank, e2.entity, e2.provider_id, e2.service_date
              from table_a e2
              inner join (
                  select min(e1.rank) as rank, e1.entity, e1.provider_id, e1.service_date
                  from table_a e1
                  where e1.site_id is not null
                  group by e1.entity, e1.provider_id, e1.service_date 
              ) as e3
              on e2.rank= e3.rank
              and e2.entity = e3.entity
              and e2.provider_id = e3.provider_id
              and e2.service_date = e3.service_date
              and e2.rank= e3.rank
              group by e2.entity, e2.provider_id, e2.service_date, e3.rank
          ) e5
          on e4.id = e5.id
          and e4.rank= e5.rank                            
      ) e6
      group by e6.category, e6.entity, e6.service_date 
  ) e7
  on e8.id = e7.id and e7.category = e8.category
  </code></pre>