title: EXISTS vs IN - which one is better in MySQL 5.5 and MySQL 5.7?
body: <p>I'm testing the two different SQL approaches (EXISTS vs IN) on both MySQL 5.7 &amp; 5.5 from a performance perspective.
  As a side note about the test, both database are on the same machine, where I activated only one of them for each test. Each of them has 4GB of memory allocated to them. I restarted the database before every test to make sure there is no caching done (at least not at the database level).</p>
  
  <p>I saw many questions here on StackOverflow, where a transformation from IN to EXISTS was helpful in terms of performance. In most threads, this was the case for older MySQL versions (ver &lt; 5.6). So my first goal was to test this theory (EXISTS is better than IN for older MySQL versions).</p>
  
  <p>Also, I've been reading that IN might have been improved in newer MySQL versions, so I wanted to see that in person.</p>
  
  <p>Therefore, to better understand which one is preferable for my future queries, I ran the following test:</p>
  
  <p>Defining the constant quantity:</p>
  
  <pre><code>SET @quantity = 50;
  </code></pre>
  
  <p><strong>EXISTS query:</strong></p>
  
  <pre><code>SELECT SQL_NO_CACHE
      c.c_first_name, c.c_birth_country
  FROM
      customer c
  WHERE
      EXISTS( SELECT 
              1
          FROM
              store_sales ss
          WHERE
              ss.ss_quantity &gt; @quantity
                  AND ss.ss_customer_sk = c.c_customer_sk)
  ORDER BY c.c_first_name DESC , c.c_birth_country DESC
  LIMIT 1000;
  </code></pre>
  
  <p><strong>The equivalent IN query:</strong></p>
  
  <pre><code>SELECT SQL_NO_CACHE
      c.c_first_name, c.c_birth_country
  FROM
      customer c
  WHERE
      c.c_customer_sk IN (SELECT 
              ss.ss_customer_sk
          FROM
              store_sales ss
          WHERE
              ss.ss_quantity &gt; @quantity)
  ORDER BY c.c_first_name DESC , c.c_birth_country DESC
  LIMIT 1000;
  </code></pre>
  
  <p><strong>The results:</strong></p>
  
  <ul>
  <li>MySQL 5.5 - EXISTS - 53 seconds</li>
  <li><p>MySQL 5.5 - IN     - 48 seconds</p></li>
  <li><p>MySQL 5.7 - EXISTS - 46 seconds</p></li>
  <li>MySQL 5.7 - IN     - 4  seconds</li>
  </ul>
  
  <p>As you can see, the results were kind of surprising:</p>
  
  <ol>
  <li>From some reason the EXISTS alternative performed worse than the IN alternative with MySQL 5.5.</li>
  <li>For MySQL 5.7, IN seems to perform significantly better than EXISTS, even though the EXPLAIN of the EXISTS alternative "looks better" than the IN alternative (see EXPLAIN outputs below).</li>
  </ol>
  
  <p>Can you please share your thoughts?</p>
  
  <p>When you're approaching to write a new query, how do you choose between IN and EXISTS? How would you guide your team? We can try them both every time, but it sounds kind of ridicules and can waste a lot of time when writing complex queries.. there should be some guideline to when each of them is better, for both MySQL &lt; 5.6 and MySQL > 5.6.</p>
  
  <p>Any documentation from MySQL that I'm missing?</p>
  
  <p>Just to close the loop with all the relevant table and explain information -</p>
  
  <p><strong>Table creation scripts with the indexes</strong> (you might see many non useful or redundant indexes here as well, but please ignore them as this is a test environment):</p>
  
  <pre><code>CREATE TABLE `customer` (
     `c_customer_sk` int(11) NOT NULL,
     `c_customer_id` char(16) NOT NULL,
     `c_current_cdemo_sk` int(11) DEFAULT NULL,
     `c_current_hdemo_sk` int(11) DEFAULT NULL,
     `c_current_addr_sk` int(11) DEFAULT NULL,
     `c_first_shipto_date_sk` int(11) DEFAULT NULL,
     `c_first_sales_date_sk` int(11) DEFAULT NULL,
     `c_salutation` char(10) DEFAULT NULL,
     `c_first_name` char(20) DEFAULT NULL,
     `c_last_name` char(30) DEFAULT NULL,
     `c_preferred_cust_flag` char(1) DEFAULT NULL,
     `c_birth_day` int(11) DEFAULT NULL,
     `c_birth_month` int(11) DEFAULT NULL,
     `c_birth_year` int(11) DEFAULT NULL,
     `c_birth_country` varchar(20) DEFAULT NULL,
     `c_login` char(13) DEFAULT NULL,
     `c_email_address` char(50) DEFAULT NULL,
     `c_last_review_date` char(10) DEFAULT NULL,
     PRIMARY KEY (`c_customer_sk`),
     KEY `c_fsd2` (`c_first_shipto_date_sk`),
     KEY `c_fsd` (`c_first_sales_date_sk`),
     KEY `c_hd` (`c_current_hdemo_sk`),
     KEY `c_cd` (`c_current_cdemo_sk`),
     KEY `c_a` (`c_current_addr_sk`),
     KEY `customer_index_1` (`c_first_name`,`c_birth_country`),
     CONSTRAINT `c_a` FOREIGN KEY (`c_current_addr_sk`) REFERENCES `customer_address` (`ca_address_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
     CONSTRAINT `c_cd` FOREIGN KEY (`c_current_cdemo_sk`) REFERENCES `customer_demographics` (`cd_demo_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
     CONSTRAINT `c_fsd` FOREIGN KEY (`c_first_sales_date_sk`) REFERENCES `date_dim` (`d_date_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
     CONSTRAINT `c_fsd2` FOREIGN KEY (`c_first_shipto_date_sk`) REFERENCES `date_dim` (`d_date_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
     CONSTRAINT `c_hd` FOREIGN KEY (`c_current_hdemo_sk`) REFERENCES `household_demographics` (`hd_demo_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  
  
  CREATE TABLE `store_sales` (
     `ss_sold_date_sk` int(11) DEFAULT NULL,
     `ss_sold_time_sk` int(11) DEFAULT NULL,
     `ss_item_sk` int(11) NOT NULL,
     `ss_customer_sk` int(11) DEFAULT NULL,
     `ss_cdemo_sk` int(11) DEFAULT NULL,
     `ss_hdemo_sk` int(11) DEFAULT NULL,
     `ss_addr_sk` int(11) DEFAULT NULL,
     `ss_store_sk` int(11) DEFAULT NULL,
     `ss_promo_sk` int(11) DEFAULT NULL,
     `ss_ticket_number` int(11) NOT NULL,
     `ss_quantity` int(11) DEFAULT NULL,
     `ss_wholesale_cost` decimal(7,2) DEFAULT NULL,
     `ss_list_price` decimal(7,2) DEFAULT NULL,
     `ss_sales_price` decimal(7,2) DEFAULT NULL,
     `ss_ext_discount_amt` decimal(7,2) DEFAULT NULL,
     `ss_ext_sales_price` decimal(7,2) DEFAULT NULL,
     `ss_ext_wholesale_cost` decimal(7,2) DEFAULT NULL,
     `ss_ext_list_price` decimal(7,2) DEFAULT NULL,
     `ss_ext_tax` decimal(7,2) DEFAULT NULL,
     `ss_coupon_amt` decimal(7,2) DEFAULT NULL,
     `ss_net_paid` decimal(7,2) DEFAULT NULL,
     `ss_net_paid_inc_tax` decimal(7,2) DEFAULT NULL,
     `ss_net_profit` decimal(7,2) DEFAULT NULL,
     PRIMARY KEY (`ss_item_sk`,`ss_ticket_number`),
     KEY `ss_s` (`ss_store_sk`),
     KEY `ss_t` (`ss_sold_time_sk`),
     KEY `ss_d` (`ss_sold_date_sk`),
     KEY `ss_p` (`ss_promo_sk`),
     KEY `ss_hd` (`ss_hdemo_sk`),
     KEY `ss_c` (`ss_customer_sk`),
     KEY `ss_cd` (`ss_cdemo_sk`),
     KEY `ss_a` (`ss_addr_sk`),
     KEY `store_sales_index_1` (`ss_quantity`,`ss_customer_sk`),
     KEY `store_sales_idx_sk_price` (`ss_item_sk`,`ss_sales_price`),
     KEY `store_sales_idx_price_sk` (`ss_sales_price`,`ss_item_sk`),
     CONSTRAINT `ss_a` FOREIGN KEY (`ss_addr_sk`) REFERENCES `customer_address` (`ca_address_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
     CONSTRAINT `ss_c` FOREIGN KEY (`ss_customer_sk`) REFERENCES `customer` (`c_customer_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
     CONSTRAINT `ss_cd` FOREIGN KEY (`ss_cdemo_sk`) REFERENCES `customer_demographics` (`cd_demo_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
     CONSTRAINT `ss_d` FOREIGN KEY (`ss_sold_date_sk`) REFERENCES `date_dim` (`d_date_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
     CONSTRAINT `ss_hd` FOREIGN KEY (`ss_hdemo_sk`) REFERENCES `household_demographics` (`hd_demo_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
     CONSTRAINT `ss_i` FOREIGN KEY (`ss_item_sk`) REFERENCES `item` (`i_item_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
     CONSTRAINT `ss_p` FOREIGN KEY (`ss_promo_sk`) REFERENCES `promotion` (`p_promo_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
     CONSTRAINT `ss_s` FOREIGN KEY (`ss_store_sk`) REFERENCES `store` (`s_store_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
     CONSTRAINT `ss_t` FOREIGN KEY (`ss_sold_time_sk`) REFERENCES `time_dim` (`t_time_sk`) ON DELETE NO ACTION ON UPDATE NO ACTION
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  </code></pre>
  
  <p><strong>Explain - MySQL 5.7 - EXISTS alternative -</strong></p>
  
  <pre><code>1   PRIMARY c       index       customer_index_1    124     1000    100.00  Using where; Using index
  2   DEPENDENT SUBQUERY  ss      ref ss_c,store_sales_index_1    ss_c    5   tpcds.c.c_customer_sk   32  50.00   Using where
  </code></pre>
  
  <p><strong>Explain - MySQL 5.7 - IN alternative -</strong></p>
  
  <pre><code>1   SIMPLE  ss      range   ss_c,store_sales_index_1    store_sales_index_1 5       1395022 100.00  Using where; Using index; Using temporary; Using filesort; Start temporary
  1   SIMPLE  c       eq_ref  PRIMARY PRIMARY 4   tpcds.ss.ss_customer_sk 1   100.00  End temporary
  </code></pre>
  
  <p><strong>Explain - MySQL 5.5 - EXISTS alternative -</strong></p>
  
  <pre><code>1   PRIMARY c   index       customer_index_1    124     1000    Using where; Using index
  2   DEPENDENT SUBQUERY  ss  ref ss_c,store_sales_index_1    ss_c    5   tpcds.c.c_customer_sk   14  Using where
  </code></pre>
  
  <p><strong>Explain - MySQL 5.5 - IN alternative -</strong></p>
  
  <pre><code>1   PRIMARY c   index       customer_index_1    124     1000    Using where; Using index
  2   DEPENDENT SUBQUERY  ss  index_subquery  ss_c,store_sales_index_1    ss_c    5   func    14  Using where
  </code></pre>