title: Mysql Index misbehave in rails
body: <p>I have an rails app hosted with Mysql, there is a <strong>reservations</strong> table with index set in column <strong>rescheduled_reservation_id</strong> (nullable).</p>
  
  <p>In my rails app there are two part to query reservation by  rescheduled_reservation_id fields as below:</p>
  
  <pre class="lang-html prettyprint-override"><code>Transit::Reservation.find_by(rescheduled_reservation_id: 25805)
  </code></pre>
  
  
  
  <p>and produce the following log output:</p>
  
  <pre class="lang-html prettyprint-override"><code>Transit::Reservation Load (60.3ms)  SELECT  `transit_reservations`.* FROM `transit_reservations` WHERE `transit_reservations`.`deleted_at` IS NULL AND `transit_reservations`.`rescheduled_reservation_id` = 25805 LIMIT 1
  </code></pre>
  
  
  
  <p>However the other part of the app:</p>
  
  <pre class="lang-html prettyprint-override"><code>Transit::Reservation.where(rescheduled_reservation_id: 25805).last
  </code></pre>
  
  
  
  <p>with the log output belows
  </p>
  
  <pre><code>Transit::Reservation Load (2.3ms)  SELECT  `transit_reservations`.* FROM `transit_reservations` WHERE `transit_reservations`.`deleted_at` IS NULL AND `transit_reservations`.`rescheduled_reservation_id` = 25805  ORDER BY `transit_reservations`.`id` DESC LIMIT 1
  </code></pre>
  
  
  
  <p>As clearly seen the first query</p>
  
  <pre><code>Transit::Reservation Load (60.3ms)  SELECT  `transit_reservations`.* FROM `transit_reservations` WHERE `transit_reservations`.`deleted_at` IS NULL AND `transit_reservations`.`rescheduled_reservation_id` = 25805 LIMIT 1
  </code></pre>
  
  <p>took up to 60ms, the index might not have been used properly comparing to 2ms in this</p>
  
  <pre><code>Transit::Reservation Load (2.3ms)  SELECT  `transit_reservations`.* FROM `transit_reservations` WHERE `transit_reservations`.`deleted_at` IS NULL AND `transit_reservations`.`rescheduled_reservation_id` = 25805  ORDER BY `transit_reservations`.`id` DESC LIMIT 1
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/3vl12.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3vl12.png" alt="enter image description here"></a></p>
  
  <p>I also tried to debug further by running <strong>explain</strong> on both queries, I got back the same result i.e the index rescheduled_reservation_id being used</p>
  
  <p><a href="https://i.stack.imgur.com/7t0JM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7t0JM.png" alt="enter image description here"></a></p>
  
  <p>Is there anyone experiencing with this issue? I am wondering whether rails mysql connection ( I am using mysql2 gem ) might cause Mysql server to not choose the right index</p>