ASIX/M02/UF2/A08/E3

Guillem Solà i Boeck (host A10BPC05)

Sobre la base de dades Documents, fer les preguntes:

Crear Index

Crear un index diferent de la PK, per cada taula.

Creo Index a la taula documents pel camp Tipus

MariaDB [documents]> CREATE INDEX IF NOT EXISTS IX_Tipus ON Document (Tipus);
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

Creo Index a la taula Tema per al camp Subtema

MariaDB [documents]> CREATE INDEX IF NOT EXISTS IX_SubTema ON Tema (SubTema);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

Per a la taula TemaDoc no creo cap Index perquè els 2 camps que formen la taula formen la PK i, per tant, ja són índex.

Modificar Index

Modificar els indexs dels camps nom per a que siguin DESC Com que no existeix una instrucció "DROP INDEX" hem d'eliminar-lo i tornar a crear de la següent manera:

Per a la taula Document:

MariaDB [documents]> ALTER TABLE Document DROP INDEX Nom;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [documents]> CREATE INDEX IF NOT EXISTS Nom ON Document (Nom DESC);
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

Per a la taula Tema:

MariaDB [documents]> ALTER TABLE Tema DROP INDEX Descripcio;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [documents]> CREATE INDEX IF NOT EXISTS Descripcio ON Tema (Descripcio DESC);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

Finalment, puc comprovar amb "SHOW INDEX FROM <taula>" els index que hi ha a les taules corresponents:

MariaDB [documents]> show index from Document;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Document |          0 | PRIMARY  |            1 | idDoc       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| Document |          1 | IX_Tipus |            1 | Tipus       | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| Document |          1 | Nom      |            1 | Nom         | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

MariaDB [documents]> show index from Tema;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Tema  |          0 | PRIMARY    |            1 | idTema      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| Tema  |          1 | IX_SubTema |            1 | SubTema     | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| Tema  |          1 | Descripcio |            1 | Descripcio  | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)