SQL query COUNT is slow. - Codes Helper - Programming Question Answer

SQL query COUNT is slow.

query the COUNT of a sentence (1), JOIN IN has 4 tables. The total number of records is 3800, and it takes 43 seconds
this is SQL:

select count(1) from (
SELECT a.id AS "id", a.batch_id AS "batchId", a.product_type AS "productType", a.retrospect_type AS "retrospectType", a.operator_name AS "operatorName", a.add_time AS "addTime", a.medicine_name AS "medicineName", a.check_result AS "checkResult", a.create_date AS "createDate", a.is_open AS "isOpen", a.remark AS "remark", 
regionale.name AS "regionaleName", 
stald.name AS "staldName", 
sorter.name AS "sorterName", 
(select GROUP_CONCAT(b.url) from retrospect_attachment b where b.bid=a.id and b.type="1" group by b.bid) as "pictures", 
(select GROUP_CONCAT(c.url) from retrospect_attachment c where c.bid=a.id and c.type="2" group by c.bid) as "vedios" 
FROM retrospect_details a 
JOIN tilbage_stald_sorter tss ON tss.id = a.stald_sorter_id 
JOIN tilbage_stald stald ON stald.id = tss.stald_id 
JOIN tilbage_regionale regionale ON regionale.id = stald.regionale_id 
JOIN tilbage_sorter sorter ON sorter.id = tss.sorter_id WHERE a.del_flag = 0
) tmp_count

this is the execution plan:

I am relatively poor on SQL, and there is nothing I can do to solve the problem. But if you want to solve the problem from sql, you can also learn some knowledge about it.
if I had to solve it myself, I might add three new fields, save the three name I need to find in the database, and look up only one table (a table that does not involve group).
Thank you for helping me out.

Mar.04,2021

if it's just for count , I think your sql is equivalent to the one below

.

SELECT count(a.id)
FROM retrospect_details a 
JOIN tilbage_stald_sorter tss ON tss.id = a.stald_sorter_id 
JOIN tilbage_stald stald ON stald.id = tss.stald_id 
JOIN tilbage_regionale regionale ON regionale.id = stald.regionale_id 
JOIN tilbage_sorter sorter ON sorter.id = tss.sorter_id WHERE a.del_flag = 0

Previous: The post data submitted by webuploader cannot be obtained by the php backend.

Next: The new version of better-scroll is used in vue, but the carousel does not take effect. Snap is written in the way of objects, and pageIndex-1 is removed.

MySQL statement to ask for help
how does sql write from the database table to the display of the page? requirement: the first-level classification needs sorting, the first-level classification shows its subcategories, and the subcategories also need sorting. ...

Sql mysql

Feb.27,2021
Mysql trigger
there are two insert statements in the mysql trigger. How to implement the second inert with the result of the first insert DELIMITER $$ USE `nightclub`$$ DROP TRIGGER *!50032 IF EXISTS * `create_histroy_room_statuses`$$ CREATE *!50017 DEFIN...

Sql mysql

Feb.28,2021
How does Mysql merge two tables
ask how to write the sql statement ...

Sql mysql

Mar.02,2021
How do Mysql: select nonclustered index columns, clustered index columns, unindexed columns and * compare performance?
has always thought that index optimization is effective in where is useless in select but it seems that there is a performance difference between select nonclustered index columns, clustered index columns, unindexed columns and select *? ...

Mysql mysql-optimized

Mar.02,2021
MySQL writes queries and asks for help?
Table T_USER T_ROLE : the following is the table creation and data script drop table if exists T_USER; CREATE TABLE T_USER( USER_ID INT, USER_NAME VARCHAR(25) ); INSERT INTO T_USER VALUES(1, USER_A ); INSERT INTO T_USER VALUES(2, USER_B )...

Sql mysql database

Mar.02,2021
Regular expressions in MYSQL
in mysql, set a regular expression to filter strings ending in number select number from test where number REGEXP xxxxxxxxx . ...

Sql mysql

Mar.04,2021
Mysql query statement
A a_id a_title a_key a_desc a_pid 1 A A A 1 B b_id b_title b_key b_desc b_pid 1 b 2 C c_id c_title c_key c_desc c_pid 1 $title= Ctitile || ...

Sql mysql php

Mar.04,2021
Can I use System.nanoTime () as the primary key ID?
for stand-alone service, the primary key ID is a long integer, which needs to be non-continuous. For example, self-increment cannot be implemented because it is continuous. I thought of using System.nanoTime () to do ID, because I haven t practiced it...

Sql mysql java

Mar.04,2021
How to print data through curl?
my mysql database has two pieces of data when I print_r, it looks like this: [{"errno":"1","errstr":"2","errfile":"3","errline":"4"},{"errno":"12",...

Php mysql mysqli curl php7

Mar.04,2021
The usage of as in sql query statement;
is currently self-learning mysql,. When querying, sometimes you need to use as aliases, but after adding as aliases to the following two query statements, why is it incorrect? 1, select cat_id,goods_name,shop_price from goods where shop_price in (sele...

Oracle sql mysql

Mar.05,2021
A SQL sentence burns the brain logic problem-ask the boss for guidance
as shown in the figure: the fields that reply_article is " 69 " and reply_type is " 0 " and reply_aims_user is null are required. then! Here s the point. Use the reply_id in the previous query field as the primary key to associate the ...

Sql mysql

Mar.06,2021
Perform a second screening from the result set
as shown in the figure, this association query is different in that it is associated with the same table indicates that table_test requirement: first query the result set of the field whose reply_type is 0, and then use the reply_id of the resul...

Nosql oracle sql mysql

Mar.07,2021
Database Operation usage of HIbernate
Which method of HIbernate is more appropriate to use in the SQL statement select name,sex from db, for example: public T findByID (ID id, I dbName) throws Exception; public List<T> findAll(I dbName) throws Exception; public List<T> findBy...

Sql mysql spring java javascript

Mar.07,2021
Realize the if judgment of where of mysql
how to realize the if judgment of mysql. There is now a table with two key fields SELECT * FROM house WHERE price= 8500 OR shoufu= 30 I think, when the $price passed in by php is empty, the sql search statement is SELECT * FROM house WHERE s...

Sql mysql php thinkphp5

Mar.07,2021
The realization scheme of the top of the article
problem description: a blog system is being developed that needs to achieve the top of an article, and then the foreground will display the article (or more than one) at the top. What I want is to add a new field to determine whether it is a top article...

Blog github sql mysql java

Mar.07,2021
The problem of obtaining values from multiple tables with SQL statements in thinkphp
Table 1 fields are as follows 2 3 1event_description_description_id2id1cel_order_item_order_item_id3id 123idnull id ask the boss for advice ...

Sql mysql php thinkphp

Mar.09,2021
What does the MySQL collation mean?
I have found many articles on utf8_unicode_ci, utf8_general_ci and utf8_bin collation on the Internet, and I generally know that ci is case ignore, etc., but I didn t mention what other collations mean . what does the utf8mb4_polish_ci collation her...

Java python sql mysql php

Mar.10,2021
Mysql merge two query results are overwritten
uses union all to merge query results when doing a comprehensive statistical function, but the result set obtained by the query is found to be overwritten sql: SELECT studyCenterName, grade, weiji, done, zong FROM ( ...

Sql mysql

Mar.10,2021
Case insensitivity of like query in MySQL5.7
for example, I like % R% answer, the query results include not only R, but also r, which is not what I want, I want to be case-sensitive. I guess it has something to do with the proofreading rules of the table. My table uses utf8mb4 encoding, and the pr...

Mysql mysql-character-set python sqlalchemy

Mar.11,2021
Why do you use a subquery? it is clear that the field in the database has a value, but it is empty.
what is the cause of this situation, and how to solve SELECT o.order_id, o.send_goods_status send_goods_status, FROM ( SELECT o.order_id, mi.STATUS send_goods_status, FROM ypb_order_info o LEFT JOIN ypb_order_merc...

Sql mysql

Mar.13,2021

Menu