exclusion query notes (sakila)

gistfile1.sql

SELECT title FROM film f JOIN inventory i USING (film_id) LEFT JOIN rental r USING (inventory_id) WHERE r.inventory_id IS NULL;

mysql> EXPLAIN SELECT title FROM film f JOIN inventory i USING (film_id) LEFT JOIN rental r USING (inventory_id) WHERE r.inventory_id IS NULL\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: f
         type: index
possible_keys: PRIMARY
          key: idx_title
      key_len: 767
          ref: NULL
         rows: 1055
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
         type: ref
possible_keys: idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: sakila.f.film_id
         rows: 2
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
         type: ref
possible_keys: idx_fk_inventory_id
          key: idx_fk_inventory_id
      key_len: 3
          ref: sakila.i.inventory_id
         rows: 1
        Extra: Using where; Using index; Not exists
3 rows in set (0.00 sec)


$ (for x in {1..1000}; do echo 'SELECT title FROM film f JOIN inventory i USING (film_id) LEFT JOIN rental r USING (inventory_id) WHERE r.inventory_id IS NULL;'; echo; done ) | ./mk-query-profiler  -u root --database=sakila 
+----------------------------------------------------------+
|                     1000 (0.0172 sec)                    |
+----------------------------------------------------------+

__ Overall stats _______________________ Value _____________
   Total elapsed time                       16.698
   Questions                              1000
     COMMIT                                  0
     DELETE                                  0
     DELETE MULTI                            0
     INSERT                                  0
     INSERT SELECT                           0
     REPLACE                                 0
     REPLACE SELECT                          0
     SELECT                               1000
     UPDATE                                  0
     UPDATE MULTI                            0
   Data into server                     131000
   Data out of server                   121605
   Optimizer cost                      3599642.685

__ Table and index accesses ____________ Value _____________
   Table locks acquired                   3000
   Table scans                            1000
     Join                                    0
   Index range scans                         0
     Join without check                      0
     Join with check                         0
   Rows sorted                               0
     Range sorts                             0
     Merge passes                            0
     Table scans                             0
     Potential filesorts                     0


SELECT title FROM film f JOIN inventory i USING (film_id) WHERE inventory_id NOT IN (SELECT inventory_id FROM rental);

mysql> EXPLAIN SELECT title FROM film f JOIN inventory i USING (film_id) WHERE inventory_id NOT IN (SELECT inventory_id FROM rental)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: f
         type: index
possible_keys: PRIMARY
          key: idx_title
      key_len: 767
          ref: NULL
         rows: 1055
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: i
         type: ref
possible_keys: idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: sakila.f.film_id
         rows: 2
        Extra: Using where; Using index
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: rental
         type: index_subquery
possible_keys: idx_fk_inventory_id
          key: idx_fk_inventory_id
      key_len: 3
          ref: func
         rows: 1
        Extra: Using index
3 rows in set (0.00 sec)

(for x in {1..100}; do echo 'SELECT title FROM film f JOIN inventory i USING (film_id) WHERE inventory_id NOT IN (SELECT inventory_id FROM rental);' ; echo; done ) | ./mk-query-profiler  -u root --database=sakila 

+----------------------------------------------------------+
|                     1000 (0.0167 sec)                    |
+----------------------------------------------------------+

__ Overall stats _______________________ Value _____________
   Total elapsed time                       17.697
   Questions                              1000
     COMMIT                                  0
     DELETE                                  0
     DELETE MULTI                            0
     INSERT                                  0
     INSERT SELECT                           0
     REPLACE                                 0
     REPLACE SELECT                          0
     SELECT                               1000
     UPDATE                                  0
     UPDATE MULTI                            0
   Data into server                     122000
   Data out of server                   119400
   Optimizer cost                            0.000

__ Table and index accesses ____________ Value _____________
   Table locks acquired                   3000
   Table scans                            1000
     Join                                    0
   Index range scans                         0
     Join without check                      0
     Join with check                         0
   Rows sorted                               0
     Range sorts                             0
     Merge passes                            0
     Table scans                             0
     Potential filesorts                     0


SELECT title FROM film f JOIN inventory i USING (film_id) WHERE (SELECT COUNT(*) FROM rental r WHERE i.inventory_id = r.inventory_id) = 0;

mysql> EXPLAIN SELECT title FROM film f JOIN inventory i USING (film_id) WHERE (SELECT COUNT(*) FROM rental r WHERE i.inventory_id = r.inventory_id) = 0\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: f
         type: index
possible_keys: PRIMARY
          key: idx_title
      key_len: 767
          ref: NULL
         rows: 1055
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: i
         type: ref
possible_keys: idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: sakila.f.film_id
         rows: 2
        Extra: Using where; Using index
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: r
         type: ref
possible_keys: idx_fk_inventory_id
          key: idx_fk_inventory_id
      key_len: 3
          ref: sakila.i.inventory_id
         rows: 1
        Extra: Using index
3 rows in set (0.00 sec)

(for x in {1..100}; do echo 'SELECT title FROM film f JOIN inventory i USING (film_id) WHERE (SELECT COUNT(*) FROM rental r WHERE i.inventory_id = r.inventory_id) = 0; '; echo; done ) | ./mk-query-profiler  -u root --database=sakila 


+----------------------------------------------------------+
|                     1000 (0.0328 sec)                    |
+----------------------------------------------------------+

__ Overall stats _______________________ Value _____________
   Total elapsed time                       33.417
   Questions                              1000
     COMMIT                                  0
     DELETE                                  0
     DELETE MULTI                            0
     INSERT                                  0
     INSERT SELECT                           0
     REPLACE                                 0
     REPLACE SELECT                          0
     SELECT                               1000
     UPDATE                                  0
     UPDATE MULTI                            0
   Data into server                     142000
   Data out of server                   119714
   Optimizer cost                            0.000

__ Table and index accesses ____________ Value _____________
   Table locks acquired                   3000
   Table scans                            1000
     Join                                    0
   Index range scans                         0
     Join without check                      0
     Join with check                         0
   Rows sorted                               0
     Range sorts                             0
     Merge passes                            0
     Table scans                             0
     Potential filesorts                     0