A problem of query Optimization

I have a table exchange 

CREATE TABLE `exchange` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rank` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `volume` varchar(255) NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=75032 DEFAULT CHARSET=utf8;

every 5 minutes, a record of data is recorded, about 209 items at a time, which is the trading volume of 209 digital currency exchanges at the current time.

I expect to find data like this:

rank name [volume (latest 144values)] timestamp

the stupid way I take now is to look it up one by one,
get the list of exchanges
one by one
select volume from exchange where exchange.name="binance" order by timestamp DESC limit 144s

performance overhead is very high, would like to ask, is there a better way?

Mysql-optimized mysql
Mar.21,2021

I have an idea. I don't know if it's feasible. The structure of exchange remains unchanged, but create a new table 

CREATE TABLE `exchange_statistics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `volume_statistics` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

where the volume_statistics field records the latest 144values after array serialization or symbol segmentation, which can be sorted out from small to large or sorted in time, and then the first element is removed every 5 minutes, and the latest element is added after it.

so that each exchange can preprocess the latest 144values, get the exchange list and directly query exchange_statistics .

exchange structure does not need to be changed to prevent future business changes. Statistics are involved.

I don't know what to do with this scheme.

the amount of data does not exceed 2 million. The query speed of yours is about 300ms. If caching is enabled, it will be even faster. If you have more than 2 million data, the speed of timestamp indexing is also very considerable. I don't know how much data you have

.

Previous: What is the logic of python * syntax when iterating over a tuple sequence?

Next: Why did the gitbook login fail?

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1b36d08-343f3.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1b36d08-343f3.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?