In-Portal Issue Tracker - In-Portal CMS
|
Viewing Issue Advanced Details |
|
ID:
|
Category:
|
Type:
|
Reproducibility:
|
Date Submitted:
|
Last Update:
|
975 |
[In-Portal CMS] Optimization |
task |
N/A |
2011-01-25 04:44 |
2012-07-25 05:33 |
|
Reporter:
|
alex |
Platform:
|
|
|
Assigned To:
|
alex |
OS:
|
|
|
Priority:
|
normal |
OS Version:
|
|
|
Status:
|
closed |
Product Version:
|
5.1.2-B1 |
|
Product Build:
|
|
Resolution:
|
fixed |
|
|
ETA:
|
none |
Fixed in Version:
|
5.2.0-B1 |
|
Reference:
|
https://groups.google.com/d/topic/in-portal-dev/DR2POG7WK1M/discussion |
Change Log Message:
|
Automatically remove used LEFT JOIN clauses from COUNT database queries |
Estimate Points:
|
2 |
|
Summary:
|
0000975: Automatically remove unused LEFT JOIN from list count database queries |
Description:
|
That's not a rare case, when data from referenced table should be displayed along with data from table being viewed.
To do that "LEFT JOIN" clause is added a SELECT query being used for data retrieval from database.
That's not a bad habit actually. But, when table contains 1 million records and has 5 LEFT JOINs on it, then it really slows COUNT database query, used to calculate pagination for that grid.
I propose that we dynamically remove "LEFT JOIN" parts of database query if JOINed table isn't used anywhere in that database query.
For example this query doesn't use JOINed table at all, so we can remove it automatically:
SELECT table1.*
FROM table1
LEFT JOIN on table2 ON table1.field = table2.field
WHERE table1.field = 'test'; |
Steps To Reproduce:
|
|
Additional Information:
|
|
Relationships | parent of | 0001359 | closed (5.2.0) | alex | In-Link | Sql error, when filtering by validation status in link validation list |
|
Attached Files:
|
remove_unused_left_joins_from_list_count_sql.patch (6,008) 2011-01-25 04:44 http://tracker.in-portal.org/file_download.php?file_id=918&type=bug remove_unused_left_joins_from_list_count_sql_520.patch (17,038) 2011-10-03 03:33 http://tracker.in-portal.org/file_download.php?file_id=1174&type=bug |
|
Issue History |
Date Modified |
Username |
Field |
Change |
2012-07-25 05:33 |
alex |
Note Added: 0005086 |
|
2012-07-25 05:33 |
alex |
Status |
resolved => closed |
2012-07-19 10:51 |
alex |
Relationship added |
parent of 0001359 |
2011-10-22 05:35 |
alex |
Estimate Points |
=> 2 |
2011-10-03 03:35 |
alex |
Note Added: 0003951 |
|
2011-10-03 03:35 |
alex |
Status |
reviewed and tested => resolved |
2011-10-03 03:35 |
alex |
Fixed in Version |
=> 5.2.0-B1 |
2011-10-03 03:35 |
alex |
Resolution |
open => fixed |
2011-10-03 03:35 |
alex |
Assigned To |
!COMMUNITY => alex |
2011-10-03 03:35 |
alex |
Changeset attached |
5.2.x r14602 |
2011-10-03 03:34 |
alex |
Note Added: 0003950 |
|
2011-10-03 03:34 |
alex |
Status |
needs testing => reviewed and tested |
2011-10-03 03:33 |
alex |
Time Estimate Removed |
1 => |
2011-10-03 03:33 |
alex |
Status |
needs work => needs testing |
2011-10-03 03:33 |
alex |
File Added: remove_unused_left_joins_from_list_count_sql_520.patch |
|
2011-09-27 05:00 |
alex |
Time Estimate Added |
1 |
2011-09-24 13:36 |
Dmitry |
Note Added: 0003897 |
|
2011-09-24 04:06 |
alex |
Note Added: 0003890 |
|
2011-09-23 22:11 |
Dmitry |
Status |
reviewed and tested => needs work |
2011-09-23 22:11 |
Dmitry |
Target Version |
Icebox => 5.2.0 |
2011-09-11 22:32 |
Dmitry |
Note Added: 0003734 |
|
2011-09-11 22:32 |
Dmitry |
Status |
needs testing => reviewed and tested |
2011-01-25 04:45 |
alex |
Note Added: 0003210 |
|
2011-01-25 04:45 |
alex |
Assigned To |
=> !COMMUNITY |
2011-01-25 04:45 |
alex |
Developer |
=> alex |
2011-01-25 04:45 |
alex |
Status |
active => needs testing |
2011-01-25 04:45 |
alex |
Reference |
=> https://groups.google.com/d/topic/in-portal-dev/DR2POG7WK1M/discussion |
2011-01-25 04:44 |
alex |
New Issue |
|
2011-01-25 04:44 |
alex |
File Added: remove_unused_left_joins_from_list_count_sql.patch |
|
2011-01-25 04:44 |
alex |
Change Log Message |
=> Automatically remove used LEFT JOIN clauses from COUNT database queries |
Notes |
|
(0003210)
|
alex
|
2011-01-25 04:45
|
|
You need to see database queries in debugger to verify, that patch worked. |
|
|
(0003734)
|
Dmitry
|
2011-09-11 22:32
|
|
Patch reviewed and tested. Ready for commit! |
|
|
(0003890)
|
alex
|
2011-09-24 04:06
|
|
You've set status to "needs work", but haven't specified what exactly needs to be changed before this code can be commited. |
|
|
(0003897)
|
Dmitry
|
2011-09-24 13:36
|
|
New patch for 5.2.x needed (PHP 5 format) |
|
|
(0003950)
|
alex
|
2011-10-03 03:34
|
|
|
|
(0003951)
|
alex
|
2011-10-03 03:35
|
|
Fix committed to 5.2.x branch. Commit Message:
Fixes 0000975: Automatically remove unused LEFT JOIN from list count database queries |
|
|
(0005086)
|
alex
|
2012-07-25 05:33
|
|
Since 5.2.0 version was released. |
|