title: MySQL - how to order multiple columns with pagination efficiently
body: <p>Say I have these four tables:</p>
  
  <pre><code>BRANCH (BRANCH_ID, CITY_ID, OWNER_ID, SPECIALTY_ID, INAUGURATION_DATE)
  CITY (CITY_ID, NAME)
  OWNER (ONWER_ID, NAME)
  SPECIALTY (SPECIALTY_ID, NAME)
  </code></pre>
  
  <p>I have a PrimeFaces datatable where I will show all branches using pagination of 50 (<code>LIMIT X, 50</code>). Today <code>BRANCH</code> has like 10000 rows. I'll join <code>BRANCH</code> with the other 3 tables because I want to show their names.</p>
  
  <p>I want to fetch the results with the following default sort:</p>
  
  <p><code>ORDER BY INAUGURATION_DATE ASC, C.NAME ASC, O.NAME ASC, S.NAME ASC</code></p>
  
  <p>Now, the user can choose to click in the header of any of these columns in my datatable, and I will query the database again making the sort he asked as the priority one. For instance, if he chose to order first by specialty name, descending, I'll do:</p>
  
  <p><code>ORDER BY S.NAME DESC, INAUGURATION_DATE ASC, C.NAME ASC, O.NAME ASC</code></p>
  
  <p>Now my question: how can I query the database with this dynamic sort always using the 4 columns, <strong>efficiently</strong>? A lot of users can be viewing this datatable in my site at the same time (like 1000 users), so using the <code>ORDER BY</code> in the SQL is <strong>very slow</strong>. I'm doing the ordering in Java, but then I cannot do the pagination correctly. How can I make this efficiently in SQL? Is creating indexes for these columns enough?</p>
  
  <p>Thanks</p>