MySql/MariaDB: Using indexes "directly" in queries?

Question

I would like to know if it is possible with MySql/MariaDB to use indexes directly in queries. Let's say we have a simple unsorted table with timestamp/value pairs:

CREATE TABLE simple (timestamp DATETIME, val INT);

Index by adding timestamp:

ALTER TABLE simple ADD INDEX ind_ts (timestamp);

We can "quickly access" some sorted order of timestamps.

Let's define a query to provide the difference in values ​​for consecutive values:

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)

Obviously, this query is conditional and expensive. A more convenient way is to add the column myindex to the table:

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

and populate the new column with the chronological order of the timestamp (e.g. via some php code)

New queries will be simpler and cheaper:

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

The new column myindex is similar to the database table index ind_ts to some extent. (Why) Is there no MySql construct to use ind_ts instead of myindex?

Answer 1

If you are using MySQL 8.0 or MariaDB 10.2 (or later), LEAD() and LAG() provide an easy way to view the before or after OK.

If you are using an old version, please perform "self-join". That is, join the table JOIN with itself. Then align the two "tables", offset by one. This may require generating a temporary table with a new AUTO_INCRMENT to provide an easy way to offset. This might be slightly better than your idea of ​​"myindex".

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

Then

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

(This will not process the first and last rows of the table.)

NOTE: You cannot use TEMPORARY TABLE because it cannot be JOINed to itself.