When SQL has external connections, pay attention to the filtering condition location. Otherwise, the webpage will be slow.

Grandma's, why are we only three days in May? The weather in May is the most suitable for traveling, but we cannot afford these tough IT guys.
As soon as I arrived at the company, the project manager found the development leader, saying that our website page was very slow and asked him to troubleshoot the problem.
As soon as I heard that the website is slow, the slow page will come to the spirit. The old brother's line is to solve the "slow" problem.
The development leader is very depressing to say that we have added memcache once every 20 minutes. Why is it still slow,
So brother asked, What SQL statements did the web page run? Can I check it out? The development Leader decisively captured the SQL.
After investigation, we found that an SQL statement was indeed slow. The SQL statement is as follows:
Copy codeThe Code is as follows:
Select *
From (select u. NAME UniversityName,
U. id UniversityId,
Count (a. SIGNUPNUMBER) playercnt
From T_ B _UNIVERSITY u
Left join T_D_EDUCATION e
On e. UNIVERSITY_ID = u. id
Left join T_D_VIDEO_PLAYER
On a. USER_ID = e. user_id
And e. ISDEFAULT = 1
And e. ISVALID = 1
And a. AUDITSTATUS = 1
And a. ISVALID = 1
Left join T_D_USER c
On a. USER_ID = c. id
And c. ISVALID = 1
Where u. REGION_CODE like '20140901'
Group by u. NAME, u. id)
Order by playercnt desc;

The execution plan is as follows:
Copy codeThe Code is as follows:
Execution Plan
----------------------------------------------------------
Plan hash value: 3938743742
Bytes --------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------------
| 0 | select statement | 142 | 10366 | 170 (3) | 00:00:03 |
| 1 | sort order by | 142 | 10366 | 170 (3) | 00:00:03 |
| 2 | hash group by | 142 | 10366 | 170 (3) | 00:00:03 |
| * 3 | hash join right outer | 672 | 49056 | 168 (2) | 00:00:03 |
| * 4 | table access full | T_D_USER | 690 | 5520 | 5 (0) | 00:00:01 |
| 5 | nested loops outer | 672 | 43680 | 162 (1) | 00:00:02 |
| * 6 | hash join outer | 672 | 37632 | 14 (8) | 00:00:01 |
| * 7 | table access full | T_ B _UNIVERSITY | 50 | 2050 | 8 (0) | 00:00:01 |
| 8 | table access full | T_D_EDUCATION | 672 | 10080 | 5 (0) | 00:00:01 |
| 9 | VIEW | 1 | 9 | 0 (0) | 00:00:01 |
| * 10 | FILTER |
| * 11 | table access full | T_D_VIDEO_PLAYER | 1 | 15 | 3 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
3-access ("A". "USER_ID" = "C". "ID" (+ ))
4-filter ("C". "ISVALID" (+) = 1)
6-access ("E". "UNIVERSITY_ID" (+) = "U". "ID ")
7-filter ("U". "REGION_CODE" LIKE '20140901 ')
10-filter ("E". "ISVALID" = 1 AND "E". "ISDEFAULT" = 1)
11-filter ("A". "USER_ID" = "E". "USER_ID" AND "A". "AUDITSTATUS" = 1 AND
"A". "ISVALID" = 1)

Can you find this SQL problem? The reason why this SQL statement runs slowly is that the developer has written the SQL statements incorrectly.
The correct statement is as follows:
Copy codeThe Code is as follows:
Select *
From (select u. NAME UniversityName,
U. id UniversityId,
Count (a. SIGNUPNUMBER) playercnt
From T_ B _UNIVERSITY u
Left join T_D_EDUCATION e
On e. UNIVERSITY_ID = u. id
And e. ISDEFAULT = 1
And e. ISVALID = 1
Left join T_D_VIDEO_PLAYER
On a. USER_ID = e. user_id
And a. AUDITSTATUS = 1
And a. ISVALID = 1
Left join T_D_USER c
On a. USER_ID = c. id
And c. ISVALID = 1
Where u. REGION_CODE like '20140901'
Group by u. NAME, u. id)
Order by playercnt desc;

The execution plan is as follows:
Copy codeThe Code is as follows:
Execution Plan
----------------------------------------------------------
Plan hash value: 2738827747
Bytes ---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ---------------------------------------------------------------------------------------------
| 0 | select statement | 142 | 11218 | 25 (16) | 00:00:01 |
| 1 | sort order by | 142 | 11218 | 25 (16) | 00:00:01 |
| 2 | hash group by | 142 | 11218 | 25 (16) | 00:00:01 |
| * 3 | hash join right outer | 301 | 23779 | 23 (9) | 00:00:01 |
| * 4 | table access full | T_D_USER | 690 | 5520 | 5 (0) | 00:00:01 |
| * 5 | hash join right outer | 301 | 21371 | 17 (6) | 00:00:01 |
| * 6 | table access full | T_D_VIDEO_PLAYER | 78 | 1170 | 3 (0) | 00:00:01 |
| * 7 | hash join outer | 301 | 16856 | 14 (8) | 00:00:01 |
| * 8 | table access full | T_ B _UNIVERSITY | 50 | 2050 | 8 (0) | 00:00:01 |
| * 9 | table access full | T_D_EDUCATION | 301 | 4515 | 5 (0) | 00:00:01 |
Bytes ---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
3-access ("A". "USER_ID" = "C". "ID" (+ ))
4-filter ("C". "ISVALID" (+) = 1)
5-access ("A". "USER_ID" (+) = "E". "USER_ID ")
6-filter ("A". "AUDITSTATUS" (+) = 1 AND "A". "ISVALID" (+) = 1)
7-access ("E". "UNIVERSITY_ID" (+) = "U". "ID ")
8-filter ("U". "REGION_CODE" LIKE '20140901 ')
9-filter ("E". "ISDEFAULT" (+) = 1 AND "E". "ISVALID" (+) = 1)

Previously, SQL runs for at least five seconds, and now 0.1 seconds can produce results.
When SQL statements have external connections, remember the filter conditions !!!
If you want to optimize SQL statements, please join the QQ Group 220761024 and indicate the requests from CSDN.