MySQL version: 5.6.36
■ Question (※ 2019/11/21 00:00 We have revised it a lot. I'm sorry.)
The performance of "sample data", "expected results", and "SQL (expectable results can be acquired but takes time)" is slow.
If i know how to improve, can you tell me?
Sample data Name, X, Y, create_time (A, 0,0, '2019-11-20 01:00'), (A, 0,1, '2019-11-20 01:10'), (B, 0,1, '2019-11-20 01:20'), (A, 0,1, '2019-11-20 01:30'), (A, 1,1, '2019-11-20 01:40'), (B, 1,1, '2019-11-20 01:50'), (A, 0,1, '2019-11-20 02:00'), (A, 0,1, '2019-11-20 03:50'), (B, 0,1, '2019-11-20 05:50')
● Expected result… I want to know the number of occurrences of the name by pattern until then | Name | X | Y | count | create_time | 0-0 | 0-1 | 1-0 | 1-1 | | A | 0 | 0 | 2019-11-20 01: 00 | 0 | 0 | 0 | 0 | | A | 0 | 1 | 2019-11-20 01: 10 | 1 | 0 | 0 | 0 | | B | 0 | 1 | 2019-11-20 01: 20 | 0 | 0 | 0 | 0 | | A | 0 | 1 | 2019-11-20 01: 30 | 1 | 1 | 0 | 0 | | A | 1 | 1 | 2019-11-20 01: 40 | 1 | 2 | 0 | 0 | | B | 1 | 1 | 2019-11-20 01: 50 | 0 | 1 | 0 | 0 | | A | 0 | 1 | 2019-11-20 02: 00 | 1 | 2 | 0 | 1 | | A | 0 | 1 | 2019-11-20 03: 50 | 1 | 3 | 0 | 1 | | B | 0 | 1 | 2019-11-20 05: 50 | 0 | 1 | 0 | 1 |
Select Name, X, Y, create_time, ( Select count (X = 0 and Y = 0) From Table as B Where A.Name = B.Name And A.create_time>B.create_time ), ( Select count (X = 0 and Y = 1) From Table as B Where A.Name = B.Name And A.create_time>B.create_time ) ( Select count (X = 1 and Y = 0) From Table as B Where A.Name = B.Name And A.create_time>B.create_time ), ( Select count (X = 1 and Y = 1) From Table as B Where A.Name = B.Name And A.create_time>B.create_time ) From Table as A (* Execution/expected results can be acquired, but it takes time)
For each combination of X and Y, I want to count data that is older than the corresponding data (create_time).
Due to the large number of combinations and rows, the query never ends.
The MySQL version above doesn't seem to work with the With clause/window function ...
I tried to divide the subquery part with Case and try to execute it in one round of the table, but it did not go well.
If i have any missing or unclear points, please let me know.
Thanks for your cooperation.
Answer # 1
First, try adding the index of (create_time, X, Y).
- improvement of query speed of mysql, handling of aggregated data to join
- Analysis of test cases for performance improvement using yield in PHP
- 3 common errors MySQL reads in Binlog logs
- Common regular expressions in js
- Detailed performance comparison of several common JSON libraries in Java
- 15 important variables you must know about MySQL performance tuning (summary)
- Method for implementing performance test of MySQL database by sysbench tool
- Basic usage analysis of MySQL performance optimization artifact Explain
- Talking about the performance of MySQL paging Limit
- Common causes and solutions of MySQL slow SQL statements
- Reasons and solutions for slow MySQL query performance and poor performance
- i want to put multiple assignment expressions in a mysql if statement, but i get an error
- Detailed reasons for php7 performance improvement
- Common usage of MySQL query conditions
- Analysis of common usage examples of MySQL process functions
- mysql - common reasons for table partitioning in sql normalization
- about regular expressions in mysql
- python - performance improvement method when fetching arbitrary data from django's model and storing it in a list
- Common pits of left-join in basic operation of MySQL conjoint table query
- mysql - command error improvement
- mysql : I want to bring back unique authors with his longest book and the title of this book. there should be 3 columns in the a
- php - [laravel] relationed data cannot be displayed in blade
- mysql - how to calculate the calories of a dish from the cooking recipe table and the ingredient table (realization of inner pro
- php - how to write sql values
- php - i want to update columns in order for comments for each specific thread acquired by sql
- get record with maximum value in specified column in mysql
- mysql - i want to avoid duplicate columns when using select * left join
- i want to check if there is a comment posted by "a person who is not the poster of the target thread" in mysql
- mysql - i want to extract 2 records from one table and select insert
- php - is there any reason why the user_login column is not unique for the table design provided by wordpress?