MariaDB Created view takes too long

I have a problem. I have a table with 6 million records in it. Every record has a column dateTime, and for my code I need the most recent 16 records in ascending order. This took too long to query directly out of the original table, so I created a view using the following query:

SELECT openTime, high, low, a, b, c, d, e FROM  Candlestick WHERE market = 'USDT' AND coin = 'ETH' AND period = '5m' ORDER BY openTime DESC LIMIT 16

This means that the view only contains 16 records. Then in my code I use the view with the following query:

SELECT high, low, a, b, c, d, e FROM vwCI_USDT_ETH_5m ORDER BY openTime ASC

This query takes all (16 records) the records and puts it in ascending order, but even for these 16 rows the query takes roughly 25 seconds shown on the image below: Is there a way to speed up this select query?

UPDATE

I created an index on the Candlestick table like @The Impaler told me to, and I am now using the following query without the view:

SELECT a.high, a.low, a.a, a.b, a.c, a.d, a.e FROM (SELECT openTime, high, low, a, b, c, d, e FROM Candlestick WHERE market = 'USDT' AND coin = 'ETH' AND period = '5m' ORDER BY openTime DESC LIMIT 16 ) AS a ORDER BY a.openTime ASC

Here are all my indexes now: But still after the index, this query takes about 20 – 25 seconds. What can I do to improve it?

The result of show create table Candlestick;:

CREATE TABLE `Candlestick` (
  `dateTimeChanged` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `openTime` bigint(20) NOT NULL,
  `closeTime` bigint(20) NOT NULL,
  `market` varchar(10) NOT NULL,
  `coin` varchar(10) NOT NULL,
  `period` varchar(10) NOT NULL,
  `open` decimal(14,6) NOT NULL DEFAULT 0.000000,
  `high` decimal(14,6) NOT NULL DEFAULT 0.000000,
  `low` decimal(14,6) NOT NULL DEFAULT 0.000000,
  `close` decimal(14,6) NOT NULL DEFAULT 0.000000,
  `volume` decimal(20,8) NOT NULL DEFAULT 0.00000000,
  `a` decimal(6,3) NOT NULL DEFAULT 0.000,
  `b` decimal(3,0) NOT NULL DEFAULT 0,
  `c` decimal(3,0) NOT NULL DEFAULT 0,
  `d` decimal(3,0) NOT NULL DEFAULT 0,
  `e` varchar(1) NOT NULL DEFAULT '0',
  `ma5` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `ema5` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `ema10` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `ema12` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `ema20` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `ema26` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `ema50` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `ema55` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `ema100` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `ema200` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `rsi14AvgGain` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `rsi14AvgLoss` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `rsi14` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `macd` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `signal` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `bbLower` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `bbMiddle` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `bbUpper` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `dmiDIPositive` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `dmiDINegative` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  `dmiADX` decimal(16,8) NOT NULL DEFAULT 0.00000000,
  PRIMARY KEY (`openTime`,`market`,`coin`,`period`) USING BTREE,
  KEY `OpenTime` (`openTime`) USING BTREE,
  KEY `MarketCoinPeriod` (`market`,`coin`,`period`) USING BTREE,
  KEY `ix1` (`market`,`coin`,`period`,`openTime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Answer

A covering index could help.

This is the final suggested index based on the review below:

create index ix3 on Candlestick (market, coin, period, openTime DESC, high, low, a, b, c, d, e);

Here’s all the SQL used in the test case (fiddle):

Working test case for MariaDB and generated sample data

The following is based on a guess at the actual create table statement, but it’s useful to discuss some of the possible index issues.

Here’s the plan with the current suggested index, and some sample data:

Here’s the plan with the descending version of the index:

create index ix2 on Candlestick (market, coin, period, openTime DESC);

Here’s the suggested covering index:

create index ix3 on Candlestick (market, coin, period, openTime DESC, high, low, a, b, c, d, e);

and with your actual table, and the previous ix1 index:

Now with the new suggested index (with openTime DESC order):

Update: MariaDB seems to support the descending index syntax, but may not fully support the feature. In more recent versions of Maria (10.5, for example) the new index (ix3) is not used for this test case.

We could force the index, if that were found to be helpful:

SELECT a.high, a.low, a.a, a.b, a.c, a.d, a.e
  FROM (
         SELECT openTime, high, low, a, b, c, d, e
           FROM Candlestick FORCE INDEX (ix3)
          WHERE market = 'USDT' AND coin = 'ETH' AND period = '5m'
          ORDER BY openTime DESC
          LIMIT 16
       ) AS a
 ORDER BY a.openTime ASC
;

If we look at the table after adding the index, we notice the DESC term is ignored:

KEY `ix3` (`market`,`coin`,`period`,`openTime`,`high`,`low`,`a`,`b`,`c`,`d`,`e`)