title: MySQL Spring complicated query - ways to order and query efficiency
body: <p>I run this complicated query on Spring JPA Repository.
  My goal is to get all info from the site table, ordering it by events severity on each site.<br />
  This is my query:</p>
  <pre><code>SELECT alls.* FROM sites AS alls JOIN 
  ( 
      SELECT distinct ets.id FROM 
      ( 
          SELECT s.id, et.`type`, et.severity_level, COUNT(et.`type`) FROM sites AS s  
              JOIN users_sites AS us ON (s.id=us.site_id)  
              JOIN users AS u ON (us.user_id=u.user_id) 
              JOIN areas AS a ON (s.id=a.site_id) 
              JOIN panels AS p ON (a.id=p.area_id) 
              JOIN events AS e ON (p.id=e.panel_id) 
              JOIN event_types AS et ON (e.event_type_id=et.id) 
          WHERE u.user_id=&quot;98765432-123a-1a23-123b-11a1111b2cd3&quot;  
          GROUP BY s.id , et.`type`, et.severity_level 
          ORDER BY et.severity_level, COUNT(et.`type`) DESC 
     ) AS ets 
  ) as etsd ON alls.id = etsd.id
  </code></pre>
  <p>The second select (the one with &quot;distinct&quot;) returns site_ids ordered correctly by severity.
  Note that there are different event_types + severity in each site, and I use pagination on the answer, so I need the distinct.</p>
  <p>The problem is - the main select doesn't keep this order.
  Is there any way to keep the order in one complicated query?</p>
  <p>Another related question - one of my ideas was making two queries:</p>
  <ol>
  <li>The &quot;select distinct&quot; query that will return me the order --&gt; saved in a list &quot;order list&quot;</li>
  <li>The main &quot;sites&quot; query (that becomes very simple) with &quot;where id in {&quot;order list&quot;}</li>
  <li>Order the second query in code by &quot;order list&quot;.</li>
  </ol>
  <p>I use the query every 10 seconds, so it is very sensitive on performance.
  What seems to be faster in this case - original complicated query or those 2?</p>
  <p>Any insight will be appreciated.
  Tnx a lot.</p>