MySql/MariaDB : Utiliser des index « directement » dans les requêtes ?

Question

J'aimerais savoir s'il est possible avec MySql/MariaDB d'utiliser des index directement dans les requêtes. Disons que nous avons une simple table non triée avec des paires horodatage/valeur :

CREATE TABLE simple (timestamp DATETIME, val INT);

Index en ajoutant un horodatage :

ALTER TABLE simple ADD INDEX ind_ts (timestamp);

Nous pouvons "accéder rapidement" à un ordre trié d'horodatages.

Définissons une requête pour fournir la différence de valeurs pour des valeurs consécutives :

SELECT 
    c.timestamp AS currenttimestamp,
    COALESCE(b.timestamp,'0000-00-00 00:00:00') AS timestampbefore,
    c.val - COALESCE(b.val,0) AS difference
FROM simple c, 
simple b 
WHERE b.timestamp = (SELECT MAX(timestamp) FROM simple h WHERE h.timestamp < c.timestamp)

Évidemment, cette requête est conditionnelle et coûteuse. Un moyen plus pratique consiste à ajouter la colonne myindex au tableau :

ALTER TABLE simple ADD COLUMN (myindex INT) AFTER timestamp;

et remplissez la nouvelle colonne avec l'ordre chronologique de l'horodatage (par exemple via du code php)

Les nouvelles requêtes seront plus simples et moins chères :

SELECT 
    c.timestamp AS currenttimestamp,
    COALESCE(b.timestamp,'0000-00-00 00:00:00') AS timestampbefore,
    c.val - COALESCE(b.val,0) AS difference
FROM simple c
LEFT JOIN simple b ON c.myindex = b.myindex+1

La nouvelle colonne myindex est quelque peu similaire à l'index de la table de base de données ind_ts. (Pourquoi) N'y a-t-il pas de construction MySql pour utiliser ind_ts au lieu de myindex ?

Answer 1

Si vous utilisez MySQL 8.0 ou MariaDB 10.2 (ou supérieur), LEAD() 和 LAG() fournit un moyen simple d'afficher les lignes avant ou après.

Si vous utilisez une ancienne version, veuillez effectuer une "auto-adhésion". Autrement dit, faites de la table JOIN 与其自身连接。然后将两个“桌子”对齐，偏移一。这可能需要生成一个带有新的AUTO_INCRMENT une table temporaire pour fournir un moyen simple de la compenser. Cela pourrait être légèrement meilleur que votre idée de "myindex".

CREATE TABLE new_table (
    myindex INT UNSIGNED AUTO_INCREMENT,
    PRIMARY KEY(myindex))
  SELECT * FROM simple;

Alors

SELECT a.ts - b.ts AS diff   -- (or whatever the math is)
    FROM new_table AS a
    JOIN new_table AS b  ON a.myindex = b.myindex - 1

(Cela ne traitera pas les première et dernière lignes du tableau.)

Remarque : Vous ne pouvez pas utiliser TEMPORARY TABLE，因为它不能JOINed sur lui-même.