title: MySQL Explain plan changing based on condition value
body: <p>Today we just noticed that two queries apparently identical were resulting in vastly different execution plans, in turn resulting in vastly different performance.
  Another startling fact is that the query aggregating over 50k+ rows runs 30x faster than the query aggregating over 600 results.
  The "fast" query runs in ~400ms and the "slow" query runs in ~10sc.</p>
  
  <p>The slow query:</p>
  
  <pre><code>SELECT account_ownership_id, count(*)
  FROM posts
  JOIN accounts ON posts.account_id = accounts.id
  JOIN platforms ON accounts.platform_id = platforms.id 
  JOIN sponsor_annotations ON sponsor_annotations.post_id = posts.id
  JOIN rightsholders_placements
  ON (rightsholders_placements.rightsholder_id = sponsor_annotations.rightsholder_id
  AND rightsholders_placements.placement_id = sponsor_annotations.placement_id)
  JOIN clients_sponsors_placements
  ON (clients_sponsors_placements.rightsholder_id = sponsor_annotations.rightsholder_id
  AND clients_sponsors_placements.sponsor_id = sponsor_annotations.sponsor_id
  AND clients_sponsors_placements.placement_id = sponsor_annotations.placement_id)
  WHERE  clients_sponsors_placements.client_id = 1125   and accounts.platform_id = 5 
  GROUP BY sponsor_annotations.account_ownership_id LIMIT 1000
  </code></pre>
  
  <p>The fast query:</p>
  
  <pre><code>SELECT account_ownership_id, count(*)
  FROM posts
  JOIN accounts ON posts.account_id = accounts.id
  JOIN platforms ON accounts.platform_id = platforms.id 
  JOIN sponsor_annotations ON sponsor_annotations.post_id = posts.id
  JOIN rightsholders_placements
  ON (rightsholders_placements.rightsholder_id = sponsor_annotations.rightsholder_id
  AND rightsholders_placements.placement_id = sponsor_annotations.placement_id)
  JOIN clients_sponsors_placements
  ON (clients_sponsors_placements.rightsholder_id = sponsor_annotations.rightsholder_id
  AND clients_sponsors_placements.sponsor_id = sponsor_annotations.sponsor_id
  AND clients_sponsors_placements.placement_id = sponsor_annotations.placement_id)
  WHERE  clients_sponsors_placements.client_id = 1125   and accounts.platform_id = 1
  GROUP BY sponsor_annotations.account_ownership_id LIMIT 1000
  </code></pre>
  
  <p>As you can see, the only difference between the two queries is on the platform id where clause. I would expect the query plans to be very similar, but they're not. And even though the slow query aggregates on much fewer rows, it takes significantly more time.</p>
  
  <p>What's also interesting is that changing the condition to be "accounts.platform_id in (1,5)" speeds up the query significantly (the query becomes as fast as the one where we're doing accounts.platform_id = 1)</p>
  
  <p>Here is the explain plan for the slow query:</p>
  
  <pre><code>+---+--------+-----------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+----+--------------------------------------------------------------------------------------------------------------------------------+-----+-------+----------------------------------------------+
  | 1 | SIMPLE | platforms                   | const  | PRIMARY                                                                                                                                                                                     | PRIMARY               | 4  | const                                                                                                                          | 1   | 100.0 | Using index; Using temporary; Using filesort |
  | 1 | SIMPLE | accounts                    | ref    | PRIMARY,fk_accounts_platforms                                                                                                                                                               | fk_accounts_platforms | 4  | const                                                                                                                          | 354 | 100.0 | Using index                                  |
  | 1 | SIMPLE | posts                       | ref    | PRIMARY,fk_posts_accounts_id                                                                                                                                                                | fk_posts_accounts_id  | 4  | sports.accounts.id                                                                                                             | 2   | 100.0 | Using index                                  |
  | 1 | SIMPLE | sponsor_annotations         | ref    | sponsor_annotations,fk_sponsor_annotations_sponsor_placements,fk_sponsor_annotations_rightsholder_placements,fk_sponsor_annotations_sponsors,fk_sponsor_annotations_account_ownership_types | sponsor_annotations   | 4  | sports.posts.id                                                                                                                | 29  | 100.0 |                                              |
  | 1 | SIMPLE | clients_sponsors_placements | eq_ref | PRIMARY,fk_client_sponsor_placements_clients_rightsholders_sponsors,fk_client_sponsor_placements_sponsor_placements                                                                         | PRIMARY               | 16 | const,sports.sponsor_annotations.placement_id,sports.sponsor_annotations.rightsholder_id,sports.sponsor_annotations.sponsor_id | 1   | 100.0 | Using index                                  |
  | 1 | SIMPLE | rightsholders_placements    | eq_ref | PRIMARY,fk_rightsholders_placements_rightsholders                                                                                                                                           | PRIMARY               | 8  | sports.sponsor_annotations.placement_id,sports.sponsor_annotations.rightsholder_id                                             | 1   | 100.0 | Using index                                  |
  +---+--------+-----------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+----+--------------------------------------------------------------------------------------------------------------------------------+-----+-------+----------------------------------------------+
  </code></pre>
  
  <p>And the explain plan for the faster query:</p>
  
  <pre><code>+---+--------+-----------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------+----+--------------------------------------------------------------------------------------------------------------------------------------------------+-----+-------+----------------------------------------------+
  | 1 | SIMPLE | platforms                   | const  | PRIMARY                                                                                                                                                                                     | PRIMARY                                   | 4  | const                                                                                                                                            | 1   | 100.0 | Using index; Using temporary; Using filesort |
  | 1 | SIMPLE | clients_sponsors_placements | ref    | PRIMARY,fk_client_sponsor_placements_clients_rightsholders_sponsors,fk_client_sponsor_placements_sponsor_placements                                                                         | PRIMARY                                   | 4  | const                                                                                                                                            | 223 | 100.0 | Using index                                  |
  | 1 | SIMPLE | rightsholders_placements    | eq_ref | PRIMARY,fk_rightsholders_placements_rightsholders                                                                                                                                           | PRIMARY                                   | 8  | sports.clients_sponsors_placements.placement_id,sports.clients_sponsors_placements.rightsholder_id                                               | 1   | 100.0 | Using index                                  |
  | 1 | SIMPLE | sponsor_annotations         | ref    | sponsor_annotations,fk_sponsor_annotations_sponsor_placements,fk_sponsor_annotations_rightsholder_placements,fk_sponsor_annotations_sponsors,fk_sponsor_annotations_account_ownership_types | fk_sponsor_annotations_sponsor_placements | 12 | sports.clients_sponsors_placements.rightsholder_id,sports.clients_sponsors_placements.sponsor_id,sports.clients_sponsors_placements.placement_id | 158 | 100.0 |                                              |
  | 1 | SIMPLE | posts                       | eq_ref | PRIMARY,fk_posts_accounts_id                                                                                                                                                                | PRIMARY                                   | 4  | sports.sponsor_annotations.post_id                                                                                                               | 1   | 100.0 |                                              |
  | 1 | SIMPLE | accounts                    | eq_ref | PRIMARY,fk_accounts_platforms                                                                                                                                                               | PRIMARY                                   | 4  | sports.posts.account_id                                                                                                                          | 1   | 100.0 | Using where                                  |
  +---+--------+-----------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------+----+--------------------------------------------------------------------------------------------------------------------------------------------------+-----+-------+----------------------------------------------+
  </code></pre>
  
  <p>How would I go about changing either my query or my schema to make sure that in both cases the execution plan is identical?</p>
  
  <p>Thanks,</p>