Indexation — Stratégies de performance

-- Créer un index simple
CREATE INDEX idx_cmd_date ON commandes(cree_a);
 
-- Créer un index composite (ordre important)
CREATE INDEX idx_cmd_user_date ON commandes(utilisateur_id, cree_a);
 
-- Lister les index d'une table
SHOW INDEX FROM commandes;
 
-- Supprimer un index
ALTER TABLE commandes DROP INDEX idx_cmd_date;
 
-- Vérifier l'utilisation d'un index par EXPLAIN
EXPLAIN SELECT * FROM commandes WHERE utilisateur_id = 42 AND cree_a > '2024-01-01';

-- Créer un index
CREATE INDEX idx_cmd_date ON commandes(cree_a);
 
-- Index Composite
CREATE INDEX idx_cmd_user_date ON commandes(utilisateur_id, cree_a);
 
-- Lister les index
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'commandes';
 
-- Supprimer un index
DROP INDEX idx_cmd_date;
 
-- Analyser l'exécution
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM commandes WHERE utilisateur_id = 42;

CREATE INDEX idx_cmd_date ON commandes(cree_a);
PRAGMA index_list('commandes');
EXPLAIN QUERY PLAN SELECT * FROM commandes WHERE cree_a > '2024-01-01';

-- ✅ Bon : equality sur utilisateur_id, range sur cree_a
CREATE INDEX idx ON commandes(utilisateur_id, cree_a);
 
-- ❌ Mauvais : la range sur cree_a coupe l'utilisation de utilisateur_id
CREATE INDEX idx ON commandes(cree_a, utilisateur_id);

-- Protège contre les doublons et accélère les recherches
CREATE UNIQUE INDEX idx_cmd_code ON commandes(code_commande);

-- MySQL : table information_schema
SELECT index_name, cardinality, seq_in_index, column_name
FROM information_schema.statistics
WHERE table_schema = 'mon_bdd' AND table_name = 'commandes'
ORDER BY index_name, seq_in_index;

-- PostgreSQL : vérifier les index jamais scannés
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- MySQL : activer la surveillance
SET GLOBAL stat_user_indexes=ON;
-- Then check performance_schema

EXPLAIN SELECT * FROM commandes WHERE utilisateur_id = 42 AND cree_a > '2024-01-01';

type: range
key: idx_cmd_user_date
rows: 15
Extra: Using index condition

EXPLAIN SELECT nom, prenom FROM utilisateurs WHERE ville = 'Paris';

type: index
key: idx_ville_nom_prenom
Extra: Using index

EXPLAIN (ANALYZE, BUFFERS)
SELECT nom, prenom FROM utilisateurs WHERE ville = 'Paris';

-> Seq Scan on utilisateurs (cost=0.00..45.00 rows=120 width=40) (actual time=0.012..0.890 rows=120 loops=1)
    Filter: (ville = 'Paris')
    Rows Removed by Filter: 9880
    Buffers: shared hit=100 dirtied=11

-- Requête courante
SELECT nom, prenom FROM utilisateurs WHERE ville = 'Paris' ORDER BY nom;
 
-- Index covering adapté
CREATE INDEX idx_u_ville_nom_prenom ON utilisateurs(ville, nom, prenom);