Creació d'índex. Optimització de consultes

https://www.section.io/engineering-education/mysql-query-optimization-using-indexes-with-examples/
https://dev.mysql.com/doc/refman/8.0/en/create-index.html
https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html

SHOW INDEX FROM yourtable;

mysql> show index from bd;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| bd    |          0 | PRIMARY  |            1 | id_bd       | A         |          10 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+


B-tree
A tree data structure that is popular for use in database indexes. The structure is kept sorted at all times, enabling fast lookup for exact matches (equals operator) and ranges (for example, greater than, less than, and BETWEEN operators). This type of index is available for most storage engines, such as InnoDB and MyISAM.


Exemple: municipis
posar un índex a municipis.municipi
i fer un order by municipi

http://www.btechsmartclass.com/data_structures/b-trees.html

To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA:

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';

use information_schema;
SELECT * FROM statistics;

https://www.youtube.com/watch?v=mTMrszfrNtI

mysql> SHOW INDEX FROM municipis;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| municipis |          0 | PRIMARY  |            1 | id_mun      | A         |        8380 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0,01 sec)

select municipi from municipis order by municipi desc;
8131 rows in set (0,01 sec)


EXPLAIN ANALYZE select municipi from municipis order by municipi desc;

| -> Sort: municipis.municipi DESC  (cost=843.75 rows=8380) (actual time=9.648..10.132 rows=8131 loops=1)
    -> Table scan on municipis  (cost=843.75 rows=8380) (actual time=0.059..2.268 rows=8131 loops=1)

són 12 ms (2.268 + 10.132)

CREATE INDEX municipi_idx ON municipis (municipi);

mysql> SHOW INDEX FROM municipis;
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| municipis |          0 | PRIMARY      |            1 | id_mun      | A         |        8380 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| municipis |          1 | municipi_idx |            1 | municipi    | A         |        8114 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0,01 sec)

select municipi from municipis order by municipi desc;
8131 rows in set (0,00 sec)

EXPLAIN ANALYZE select municipi from municipis order by municipi desc;
-> Index scan on municipis using municipi_idx (reverse)  (cost=843.75 rows=8380) (actual time=0.077..4.682 rows=8131 loops=1)

són 4.6 ms. Per tant, són unes 3 vegades més ràpid.

Actual time to get first row (in milliseconds)
Actual time to get all rows (in milliseconds)
Actual number of rows read
Actual number of loops

DROP INDEX municipi_idx ON municipis;

EXPLAIN ANALYZE select municipi from municipis order by municipi desc;
| -> Sort: municipis.municipi DESC  (cost=843.75 rows=8380) (actual time=8.231..8.570 rows=8131 loops=1)
    -> Table scan on municipis  (cost=843.75 rows=8380) (actual time=0.101..1.969 rows=8131 loops=1)

Torna a trigar de l'ordre de 12 ms

                     LUR
                /            \
          DAN                     PRI
        /     \                 /       \
    CAL        EBR         MIT             TRO
   /    \         \            \          /    \
BAT      CRE      FCR          NOL      SAT     ZAT

---

creat per Joan Quintana Compte, febrer 2022