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?

About sql Optimization
now I have an array, which contains about 100000 of the data, all personId,. I now need to use this ID to query other data in another table. Is there any good way to make the query more efficient? Whether IN can be used or not, although it is easy to wri...

Mysql-optimized mysql php

Mar.06,2021
Mysql batch data update and insertion pit
has such a logic, a piece of data, if there is in the database, then update, if it does not exist, then insert. But now there are ten thousand. So how to accomplish the task efficiently? ...

Mysql-Index mysql-optimized mysql

Mar.14,2021
The datetime decimal part of MySQL is subtle, isn't it?
< H1 > question < H1 > the data table needs a column to keep the current time, and then it needs to be accurate to milliseconds , and then search it. It is found that datetime can take decimals. According to this blog TIMESTAMP(3) DATETIME(3)3 T...

Mysql-optimized mysql

Mar.22,2021
How to index the mysql time field
when querying, you need to query the scope according to the time field, timestamp type. Statement does not have subqueries, associated queries, only look up this table. it takes about 4.5 seconds to find 50, 000 results. This field has been indexed, bu...

Mysql-Index mysql-optimized mysql

Mar.31,2021
Doubts about subqueries and table join queries
doubts about subqueries and table join queries there are two tables, user (user) and user_dictionary (user Dictionary), and then my colleague struggles with me with a problem. Forgive me for this scum who doesn t know . related codes user: industr...

Mysql-optimized mysql

Apr.01,2021
Database thread pool application thread pool what is the difference and how to configure it.
problem description recently, I have been optimizing the related business of the system learn that you can improve the performance of db through thread pooling here comes the problem mysql has its own thread pool. You can add parameters such as ...

Web thread-pool mysql-optimized mysql

Apr.11,2021
How to optimize the Voting problem in mysql with High concurrency
for example, ten thousand people vote for one person what I do is to read the number of votes first and then get the votes + 1 , but if ten thousand people vote at the same time, the number of votes and updates must be incorrect . The code is as foll...

Php php-Framework mysql-optimized mysql

May.15,2021
When entering data, process string at the same time through php or mysql?
reason suppose I want to input all the data of a number table, how can I change the value of this field to B if there is A? INSERT INTO `wp_posts` (`ID`, `content`) VALUES (13335, <h3>< h3> r n[embed]https: www.instagram.com p Bc...

Mysql-optimized mysqli mysql php7 php

Jul.10,2021
Is MySQL slow to query for the first time?
problem description there is a user table with in it. uid regdate reg_package reg_channel ... now I have a SQL query for user data SELECT * FROM `user` ORDER BY `regdate` DESC LIMIT 0,50; there is a multi-column index on this table. i...

Mysql-Index mysql-optimized mysql

Jul.20,2021
A joint survey of several big data scales
During the interview today, the interviewer asked a question, table a 100W, table b 500W. If table an and table b are checked together, the query speed is very slow, how to optimize it? the interviewer said the index was perfect. I didn t answer at th...

Mysql-optimized mysql

Aug.13,2021
Let's come in and see how this sql function is optimized.
CREATE DEFINER=`root`@`%` FUNCTION `is_mixed`(str1 TEXT, str2 TEXT) RETURNS tinyint(4) BEGIN DECLARE ismixed TINYINT DEFAULT 0; set ismixed:=(select concat(str1, , ) regexp concat(replace(str2, , , ,| ), , )); RETURN ismixed; EN...

Mysql-optimized mysql

Sep.07,2021
MySQL Error: Lock wait timeout exceeded?
problem description specific error message is General error: 1205 Lock wait timeout exceeded; try restarting transaction the reason for this problem is that there is a process that has been occupying the lock and does not release it. How do you repr...

Mysql-optimized mysql

Nov.01,2021
How can Mysql: efficiently add fields to a table with millions of rows of data?
use directly alter table order_log add outdated int default 0 null; it hasn t been over for a long time is there any way to speed it up? ...

Mysql-optimized mysql

Nov.09,2021
Is the performance of mysql: limit much worse than that of between or in?
for example, select * from table limit 100 if there are 1 million data in the table, , then this statement will scan the whole table, filter out all the 1 million data that meets the requirements , and then fetch the first 1 million rows . and if you ...

Mysql-optimized mysql

Nov.15,2021
Million-level mysql database uses count, group, order query is very slow, how to optimize
the database currently has 1 million data (it has been running for about half a month) the following sentence takes 6 seconds to run (1 million results for where alone), and both user_id and time are indexed ...

Php mysql-Index mysql-optimized mysql

May.25,2022
For a table with 600000 data, how to optimize the use of WHERE + ORDER BY SQL?
Boss, the following sentence SQL, the execution time has reached 6s, what should I do? in the end, I chose the scheme of "create a separate index " with the least impact. not sure this is the best result! just because of my environment, comprehens...

Mysql-optimized mysql

Jun.09,2022