In-Portal Issue Tracker - In-Portal CMS
Viewing Issue Advanced Details
975 [In-Portal CMS] Optimization task N/A 2011-01-25 04:44 2012-07-25 05:33
alex  
alex  
normal  
closed 5.1.2-B1  
fixed  
 
none 5.2.0-B1  
https://groups.google.com/d/topic/in-portal-dev/DR2POG7WK1M/discussion
Automatically remove used LEFT JOIN clauses from COUNT database queries
2
0000975: Automatically remove unused LEFT JOIN from list count database queries
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';
parent of 0001359closed  (5.2.0)alex In-Link Sql error, when filtering by validation status in link validation list 
patch 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
patch 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
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   
Will test all together.
(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.