Qüestionari: Creació d'índex. Optimització de consultes

sakila.film

mysql> SHOW INDEX FROM film;

| film  |          1 | idx_title                   |            1 | title
| film  |          1 | idx_fk_language_id          |            1 | language_id

mysql> SHOW INDEX FROM film_category;
+---------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table         | Non_unique | Key_name                  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| film_category |          0 | PRIMARY                   |            1 | film_id     | A         |        1000 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| film_category |          0 | PRIMARY                   |            2 | category_id | A         |        1000 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| film_category |          1 | fk_film_category_category |            1 | category_id | A         |          16 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |

mysql> SHOW INDEX FROM category;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| category |          0 | PRIMARY  |            1 | category_id | A         |          16 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |

Anar a la definició de les taules (script ...) i veure on es creen aquests índex

Mirar quan triga la consulta: llistat de títols de pel·lícules i categories

mysql> select title, name from film f inner join film_category fc using (film_id) inner join category c using (category_id) limit 10;
+---------------------+--------+
| title               | name   |
+---------------------+--------+
| AMADEUS HOLY        | Action |
| AMERICAN CIRCUS     | Action |
| ANTITRUST TOMATOES  | Action |
| ARK RIDGEMONT       | Action |
| BAREFOOT MANCHURIAN | Action |
| BERETS AGENT        | Action |
| BRIDE INTRIGUE      | Action |
| BULL SHAWSHANK      | Action |
| CADDYSHACK JEDI     | Action |
| CAMPUS REMEMBER     | Action |
...


EXPLAIN ANALYZE select title, name from film f inner join film_category fc using (film_id) inner join category c using (category_id);

| -> Nested loop inner join  (cost=455.97 rows=1000) (actual time=0.093..3.931 rows=1000 loops=1)
    -> Nested loop inner join  (cost=105.97 rows=1000) (actual time=0.079..0.904 rows=1000 loops=1)
        -> Table scan on c  (cost=1.85 rows=16) (actual time=0.037..0.047 rows=16 loops=1)
        -> Index lookup on fc using fk_film_category_category (category_id=c.category_id)  (cost=0.65 rows=62) (actual time=0.020..0.043 rows=62 loops=16)
    -> Single-row index lookup on f using PRIMARY (film_id=fc.film_id)  (cost=0.25 rows=1) (actual time=0.002..0.003 rows=1 loops=1000)

total: 3.931 + 0.904 + 0.047 + 0.043 + 0.003 = 4,928 ms

ens carreguem l'índex fk_film_category_category de la taula film_category

però per carregar-nos l'index abans ens hem de carregar la foreign key. Per tant, hem d'executar aquestes dues instruccions.

alter table film_category drop foreign key fk_film_category_category;
DROP INDEX fk_film_category_category ON film_category;

EXPLAIN ANALYZE select title, name from film f inner join film_category fc using (film_id) inner join category c using (category_id);

| -> Nested loop inner join  (cost=801.00 rows=1000) (actual time=0.079..4.845 rows=1000 loops=1)
    -> Nested loop inner join  (cost=451.00 rows=1000) (actual time=0.069..2.353 rows=1000 loops=1)
        -> Index scan on fc using PRIMARY  (cost=101.00 rows=1000) (actual time=0.056..0.490 rows=1000 loops=1)
        -> Single-row index lookup on c using PRIMARY (category_id=fc.category_id)  (cost=0.25 rows=1) (actual time=0.001..0.001 rows=1 loops=1000)
    -> Single-row index lookup on f using PRIMARY (film_id=fc.film_id)  (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=1000)

4,845 + 2,353 + 0,490 + 0,001 + 0,002 = 7,691

Torna a restaurar les restriccions: (creem la clau forànea, i al mateix temps es crea l'index)

ALTER TABLE film_category ADD CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category(category_id);

---

creat per Joan Quintana Compte, febrer 2022