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;

---

creat per Joan Quintana Compte, febrer 2022