MariaDB Created view takes too long-CodePudding

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

CodePudding user response：

The query is quite simple. You don't need a view, but a good index for it.

The index that will improve the performance of that query is:

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

CodePudding user response：

Even when you are querying your view. It's still the querying the table source table, getting all the rows, sorting in descending order of openTime, only then selecting top 16 rows.

After that you it's sorting the selected 16 digits in ascending order of openTime.

I agree with @The Impaler. You might need an appropriate index on that table.

CodePudding user response：

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):

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`)