title: mysql - Optimizing ORDER BY COALESCE on joined table column
body: <p>EDITED: added full query by request.</p>
  
  <p>In essence I have a table of posts linked one to many to a table of reposts, akin to Twitter. I want to load the posts ordered by the time of the repost (if present) or the time of the original post. However, the ordering process is very slow using a single query (probably do the the fact that COALESCE(x, y) doesn't make full use of MySQL indexes). The time column on both relevant tables is indexed.</p>
  
  <p>My query looks something like this.</p>
  
  <pre><code>SELECT * FROM Post p LEFT JOIN p.reposts ON ... WHERE ... 
  ORDER BY COALESCE(r.time, p.time) LIMIT 0, 10
  </code></pre>
  
  <p>More precisely (pseudo-ish) since I'm using a DAL:</p>
  
  <pre><code>SELECT * FROM Post p LEFT JOIN p.reposts repost ON (p.id = repost.post_id AND    
  repost.time = (
    SELECT MIN(r.time) FROM Repost r WHERE p.id = r.post_id
    AND r.user_id IN (1, 2, 3...) AND r.user_id NOT IN (4, 5, 6...))
  ))
  WHERE (repost IS NOT NULL OR p.author_id IN (1, 2, 3...)) 
  AND p.author_id NOT IN (4, 5, 6...)
  ORDER BY COALESCE(repost.time, p.time) LIMIT 0, 10
  </code></pre>
  
  <p>In the above, the ON clause ensures at most one repost (the one I want) is joined. COALESCE is necessary because r may be NULL if a post has not been reposted. The query behaves as expected - fast when ORDER BY clause is omitted, or used only on an indexed column like p.time. This is to be expected since the Post table is large 100k+ rows.</p>
  
  <p><strong>Query Explanation</strong></p>
  
  <p>EDIT: better explanation of what query should do. It's worth noting the logic here works - I get the data I want. The problem is that applying the ORDER BY clause causes the query to run about 50x slower because MySQL can't use the indexes with COALESCE on a joined table.</p>
  
  <ul>
  <li>Load a list of 10 posts that are either authored by a set of users (followed) or reposted by the same set (followed), ordered by most recent.</li>
  <li>Posts should be ordered by either the time of the post or the first repost.</li>
  <li><p>Ignore posts and reposts by users in a different set (blocked)</p></li>
  <li><p>Get posts: SELECT from posts</p></li>
  <li>Get the earliest repost by a user in the followed set: LEFT JOIN ON... r.time = (SELECT MIN(r.time)...)</li>
  <li>Filter out posts not authored or reposted by users in the followed set: WHERE (repost IS NOT NULL...)</li>
  <li>Order be the first repost (if it exists) or the publication time: ORDER BY COALESCE(repost.time, p.time)</li>
  <li>Load at most 10 posts: LIMIT 0, 10 </li>
  </ul>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I found that:</p>
  
  <pre><code>...ORDER BY repost.time DESC
  </code></pre>
  
  <p>Produces slow results as well unless I also add:</p>
  
  <pre><code>...WHERE repost.id IS NOT NULL...
  </code></pre>
  
  <p>In which case the query is fast. This leads me to believe that the real problem is sorting on nullable column indexes. I also tried:</p>
  
  <pre><code>... ORDER BY CASE WHEN repost.id IS NULL p.time ELSE repost.time END DESC
  </code></pre>
  
  <p>Which didn't help.</p>
  
  <p><strong>UPDATE 2</strong></p>
  
  <p>Due to the fact that MySQL uses b-trees for its indexes, it seems it'll be impossible to leverage the indexes in the way I want. Thus my current best idea is to treat each original post as a "repost" by its author, then perform my select and order on the repost table, e.g.</p>
  
  <pre><code>SELECT * FROM Repost r LEFT JOIN r.post ON ... WHERE ... ORDER BY r.time DESC
  </code></pre>