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

Joan: /* Taques a realitzar. Entrega */

2022-02-14T20:08:39Z

Taques a realitzar. Entrega

Joan a 20:07, 14 feb 2022

2022-02-14T20:07:55Z

Joan a 20:07, 14 feb 2022

2022-02-14T20:07:43Z

Joan: Es crea la pàgina amb «

 sakila.film  mysql> SHOW INDEX FROM film;  | film  |          1 | idx_title                   |            1 | title | film  |          1 | idx_fk_language_id...».

2022-02-14T12:39:15Z

Pàgina nova

<pre>
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);
</pre>

{{Autor}}, febrer 2022

@@ Línia 91: / Línia 91: @@
 =Taques a realitzar. Entrega=
-Executa les anteriors comandes pas a pas, entenent bé el que fas. Entrega un pdf amb les captures de pantalla i comentaris oportuns, demostrant que ho has realitzat de forma individual i en la teva màquina.
+Executa les anteriors comandes pas a pas, entenent bé el que fas.
 {{Autor}}, febrer 2022

← Versió més antiga		Revisió del 20:07, 14 feb 2022
Línia 1:		Línia 1:
		+	__TOC__
	=Introducció=		=Introducció=
	A la part teòrica hem treballat sobre la taula ''municipis'' de la base de dades ''municipis''. Ara treballarem sobre la base de dades ''sakila'', i les taules ''film'', ''category'' i ''film_category''. Evidentment, és una relació N:M, on una peli pot tenir vàries categories, i una categoria té moltes pelis.		A la part teòrica hem treballat sobre la taula ''municipis'' de la base de dades ''municipis''. Ara treballarem sobre la base de dades ''sakila'', i les taules ''film'', ''category'' i ''film_category''. Evidentment, és una relació N:M, on una peli pot tenir vàries categories, i una categoria té moltes pelis.

@@ Línia 1: / Línia 1: @@
 <pre>
 mysql> SHOW INDEX FROM film;
 | film  |          1 | idx_title                   |            1 | title
 | film  |          1 | idx_fk_language_id          |            1 | language_id
+</pre>
 mysql> SHOW INDEX FROM film_category;
 +---------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
@@ Línia 14: / Línia 17: @@
 | 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       |
+</pre>
 mysql> SHOW INDEX FROM category;
 +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
@@ Línia 20: / Línia 24: @@
 +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
 | 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
+Volem fer la consulta: llistar de títols de pel·lícules i categories. Són dos inner joins:
+<pre>
 mysql> select title, name from film f inner join film_category fc using (film_id) inner join category c using (category_id) limit 10;
 +---------------------+--------+
@@ Línia 40: / Línia 43: @@
 | CAMPUS REMEMBER     | Action |
 ...
+</pre>
+I aquesta consulta s'executa prou ràpid perquè hi ha els índex ben definits (que es van crear de forma automàtica):
 EXPLAIN ANALYZE select title, name from film f inner join film_category fc using (film_id) inner join category c using (category_id);
@@ Línia 49: / Línia 53: @@
          -> 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)
+</pre>
 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
+Ara ens carregarem l'índex ''fk_film_category_category'' de la taula ''film_category'', que és la clau forànea que apunta a ''category''. Per carregar-nos l'index abans ens hem de carregar la foreign key. Per tant, hem d'executar aquestes dues instruccions:
+<pre>
-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;
+</pre>
 EXPLAIN ANALYZE select title, name from film f inner join film_category fc using (film_id) inner join category c using (category_id);
@@ Línia 66: / Línia 77: @@
          -> 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)
+</pre>
 ,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)
+Per deixar-ho com ho teníem al principi, tornem a restaurar les restriccions: (creem la clau forànea, i al mateix temps es crea l'index)
+<pre>
 ALTER TABLE film_category ADD CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category(category_id);
 </pre>
 {{Autor}}, febrer 2022