title: MySQL BETWEEN two datetimes does not use INDEX
body: <pre><code>explain
  SELECT COUNT(*) AS Count, CreatedBy
  FROM `Notes`
  INNER JOIN Users ON UserID = CreatedBy
  INNER JOIN UserRoles ON URoleID = RoleID AND RoleID = 1
  WHERE NoteVisible = 1 AND NoteText NOT LIKE '%SOME KEYWORD%' 
        AND Created BETWEEN '2014-02-24 00:00:00' AND '2014-02-24 23:59:59' 
  GROUP BY CreatedBy
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/JaYvq.png" alt="enter image description here"></p>
  
  <p>As you see <strong>ref</strong> is NULL and goes through <strong>23</strong> rows instead of just going through <strong>1</strong> row. Now for this example this is fast but when I do range of 1-2 month the rows becomes <strong>>10000</strong> and it slows down the page alot and locks up tables.</p>
  
  <p><strong>NOTE</strong> If I remove the <strong>00:00:00</strong> and <strong>23:59:59</strong> then it uses index it only goes through 1 row but I need to select all data for entire day starting at <strong>00:00</strong> and ending at <strong>23:59</strong>.</p>
  
  <p>Please help me restructure this query to fix this issue or suggest any possible solutions. thank you.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>Replacing <strong>BETWEEN</strong> by <strong>&lt;</strong> or <strong>></strong> or <strong>&lt;=</strong> or <strong>>=</strong> does not fix the issue</p>