Automatically remove unused LEFT JOIN from list count database queries
Imported form: http://groups.google.com/group/in-portal-dev/browse_thread/thread/0d1d8f386ed62b53#
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';Related Tasks
Comments
This will affect both admin and front-end. Only SQLs, that are used to get
data to be displayed in grids are affected.
Haven't tested, when subselect is used.
On Fri, Jan 28, 2011 at 5:44 PM, Dmitry A. <dandre...@gmail.com> wrote:
> Hi Alex,
> First of all, thanks for the patch!
> I suppose we are talking about Admin part at all?
> Also, this accounts for Catalog Permissions if we are counting Category
> Items. Please correct me if I am wrong.
> DA
Can you give me an example of SUB-SELECT in Grids.
I don't think we are having any at the moment in default installation, do
we?
DA
Hi Alex,
First of all, thanks for the patch!
I suppose we are talking about Admin part at all?
Also, this accounts for Catalog Permissions if we are counting Category
Items. Please correct me if I am wrong.
DA