Brett Harvey <[EMAIL PROTECTED]> wrote on 03/10/2005 01:24:54 PM:
> I have a query that worked just fine in Mysql 3.x and 4.0. It no
> longer works in 4.1. I receive the "The SELECT would examine more
> than MAX_JOIN_SIZE rows; check your WHERE and use SET
> SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay"
> error
>
> My max_join_size is set at the default 4294967295. It appears that
> is also the max, correct? When I set it lower, it goes lower. When I
> set it higher it reverts to max_join_size=4294967295.
>
> I've simplified the fields being returned for this example. Many more
> fields are returned. Additionally, in this case, only one record is
> being found. However, in most cases, one hundred or so are being
> returned.
>
> This is my sql statement:
>
> SELECT PlanBudget.SN
> FROM FOO.PlanBudget
> LEFT JOIN FOO.Clients ON PlanBudget.Client_No=Clients.Client_No
> LEFT JOIN FOO.UserList ON Clients.ULno=UserList.ULno
> LEFT JOIN FOO.Users ON UserList.UserNo=Users.UserNo
> LEFT JOIN FOO.PGrps ON if(
> Clients.ProjectGroupsOpt="Standard",PlanBudget.PGrp_No=PGrps.PGrp_No
> And PGrps.Client_No=0 And PGrps.Client_Type=Clients.Client_Type,
> PlanBudget.PGrp_No=PGrps.PGrp_No And
> PlanBudget.Client_No=PGrps.Client_No )
> LEFT JOIN FOO.YrOpts ON if(
> Clients.YrOptOpt="Standard",PlanBudget.Year_Do_no=YrOpts.Yr_No And
> YrOpts.Client_No=0 And YrOpts.Client_Type=Clients.Client_Type,
> PlanBudget.Year_Do_no=YrOpts.Yr_No And
> PlanBudget.Client_No=YrOpts.Client_No )
> LEFT JOIN FOO.Priorities ON if(
> Clients.PrioritiesOpt="Standard",PlanBudget.Priority_No=Priorities.
> Priority_No
> And Priorities.Client_No=0 And
> Priorities.Client_Type=Clients.Client_Type,
> PlanBudget.Priority_No=Priorities.Priority_No And
> PlanBudget.Client_No=Priorities.Client_No )
> LEFT JOIN FOO.Fund_Srcs ON if(
>
Clients.FundSourcesOpt="Standard",PlanBudget.FundSrc_no=Fund_Srcs.Fund_Src_no
> And Fund_Srcs.Client_No=0 And
> Fund_Srcs.Client_Type=Clients.Client_Type,
> PlanBudget.FundSrc_no=Fund_Srcs.Fund_Src_no And
> PlanBudget.Client_No=Fund_Srcs.Client_No )
> LEFT JOIN FOO.Rtypes ON if(
> Clients.RtypesOpt="Standard",PlanBudget.RMType_no=Rtypes.Rmtype_no
> And Rtypes.Client_No=0 And Rtypes.Client_Type=Clients.Client_Type,
> PlanBudget.RMType_no=Rtypes.Rmtype_no And
> PlanBudget.Client_No=Rtypes.Client_No )
> LEFT JOIN FOO.Users Fac_Admin_No ON PlanBudget.
> Fac_Admin_No=Fac_Admin_No.UserNo
> LEFT JOIN FOO.Users Fac_Manager_No ON
> PlanBudget.Fac_Manager_No=Fac_Admin_No.UserNo
> WHERE PlanBudget.constant=1 and PlanBudget.uniqueid =
> "RBB43M2VZIATIQ45VGKR-0A0000021b38e22F87KmO19132B9"
>
>
> The Explain of the select says:
>
> table type possible_keys key
> key_len ref rows Extra
> PlanBudget ref UniqueID UniqueID
> 50 const 1 Using where
> Clients ref Client_No,Client_No_2 Client_No
> 2 FOO.PlanBudget.Client_No 1
> UserList ref ULno,ULno_2 ULno
> 2 FOO.Clients.ULno 1
> Users ref UserNo UserNo 2
> FOO.UserList.UserNo 1 Using index
> PGrps index Client_No_3 259
> 116 Using index
> YrOpts index Client_No_3 259
> 81 Using index
> Priorities index Client_No_3
> 259 42 Using index
> Fund_Srcs index Client_No_3
> 259 44 Using index
> Rtypes index Client_Type 259
> 100 Using index
> Fac_Admin_No ref UserNo UserNo 2
> FOO.PlanBudget.Fac_Admin_No 1 Using index
> Fac_Manager_No index UserNo 2
> 111 Using index
>
>
> What, if anything, can I do? As I mentioned, it worked fine in Mysql
> 3.x and 4.0. It's not functioning in Mysql 4.1
>
>
> Thanks!
>
>
>
> --
>
--------------------------------------------------------------------------------
> /Brett C. Harvey;
> /Creative-Pages.Net, President;
> /Facility Management Systems, CTO (www.fmsystems.biz);
> /Lasso Partner Association Member ID #LPA135259
> (www.omnipilot.com/www.lassopartner.com);
>
--------------------------------------------------------------------------------
First I would fix your SQL statement so that it used the UNION keyword to
eliminate the IF from your ON clauses. Then try again.
(SELECT PlanBudget.SN
FROM FOO.PlanBudget
LEFT JOIN FOO.Clients
ON PlanBudget.Client_No=Clients.Client_No
LEFT JOIN FOO.UserList
ON Clients.ULno=UserList.ULno
LEFT JOIN FOO.Users
ON UserList.UserNo=Users.UserNo
LEFT JOIN FOO.PGrps
ON PlanBudget.PGrp_No=PGrps.PGrp_No
AND Clients.ProjectGroupsOpt="Standard"
AND PGrps.Client_No=0
And PGrps.Client_Type=Clients.Client_Type
LEFT JOIN FOO.YrOpts
ON if(Clients.YrOptOpt="Standard"
AND PlanBudget.Year_Do_no=YrOpts.Yr_No
And YrOpts.Client_No=0
And YrOpts.Client_Type=Clients.Client_Type
LEFT JOIN FOO.Priorities
ON Clients.PrioritiesOpt="Standard"
AND PlanBudget.Priority_No=Priorities.Priority_No
And Priorities.Client_No=0
And Priorities.Client_Type=Clients.Client_Type
LEFT JOIN FOO.Fund_Srcs
ON Clients.FundSourcesOpt="Standard"
AND PlanBudget.FundSrc_no=Fund_Srcs.Fund_Src_no
And Fund_Srcs.Client_No=0
And Fund_Srcs.Client_Type=Clients.Client_Type
LEFT JOIN FOO.Rtypes
ON Clients.RtypesOpt="Standard"
AND PlanBudget.RMType_no=Rtypes.Rmtype_no
And Rtypes.Client_No=0
And Rtypes.Client_Type=Clients.Client_Type
LEFT JOIN FOO.Users Fac_Admin_No
ON PlanBudget.Fac_Admin_No=Fac_Admin_No.UserNo
LEFT JOIN FOO.Users Fac_Manager_No
ON PlanBudget.Fac_Manager_No=Fac_Admin_No.UserNo
WHERE PlanBudget.constant=1
and PlanBudget.uniqueid =
"RBB43M2VZIATIQ45VGKR-0A0000021b38e22F87KmO19132B9")
UNION
(SELECT PlanBudget.SN
FROM FOO.PlanBudget
LEFT JOIN FOO.Clients
ON PlanBudget.Client_No=Clients.Client_No
LEFT JOIN FOO.UserList
ON Clients.ULno=UserList.ULno
LEFT JOIN FOO.Users
ON UserList.UserNo=Users.UserNo
LEFT JOIN FOO.PGrps
ON Clients.RtypesOpt<>"Standard"
AND PlanBudget.PGrp_No=PGrps.PGrp_No
And PlanBudget.Client_No=PGrps.Client_No
LEFT JOIN FOO.YrOpts
ON Clients.RtypesOpt<>"Standard"
AND PlanBudget.Year_Do_no=YrOpts.Yr_No
And PlanBudget.Client_No=YrOpts.Client_No
LEFT JOIN FOO.Priorities
ON Clients.RtypesOpt<>"Standard"
AND PlanBudget.Priority_No=Priorities.Priority_No
And PlanBudget.Client_No=Priorities.Client_No
LEFT JOIN FOO.Fund_Srcs
ON Clients.RtypesOpt<>"Standard"
AND PlanBudget.FundSrc_no=Fund_Srcs.Fund_Src_no
And PlanBudget.Client_No=Fund_Srcs.Client_No
LEFT JOIN FOO.Rtypes
ON Clients.RtypesOpt<>"Standard"
AND PlanBudget.RMType_no=Rtypes.Rmtype_no
And PlanBudget.Client_No=Rtypes.Client_No
LEFT JOIN FOO.Users Fac_Admin_No
ON PlanBudget.Fac_Admin_No=Fac_Admin_No.UserNo
LEFT JOIN FOO.Users Fac_Manager_No
ON PlanBudget.Fac_Manager_No=Fac_Admin_No.UserNo
WHERE PlanBudget.constant=1
and PlanBudget.uniqueid =
"RBB43M2VZIATIQ45VGKR-0A0000021b38e22F87KmO19132B9");
IF that doesn't work, I would try to make what I call "a short list" in a
temporary table then LEFT JOIN all of those other table to the temp table
so that you end up with all of the data you originally needed.
You could rewrite your query this way:
CREATE TEMPORARY TABLE tmpBudget
SELECT * from PlanBudget
WHERE PlanBudget.constant=1
and PlanBudget.uniqueid =
"RBB43M2VZIATIQ45VGKR-0A0000021b38e22F87KmO19132B9");
(
SELECT ... your list of fields ...
FROM tmpBudget
LEFT JOIN ... (all of your other Joins)
)UNION (
SELECT ...
);
That way you pre-eliminate the records you would have thrown out anyway
and you SERIOUSLY reduce the size of your internally created JOIN table.
For instance if you had 5000 records in PlanBudget and you would normally
get back 100, that's 4900 less records x # of records in each LEFT JOIN
table saved from needing to be processed. With this many joins, you will
save a cubic butt-load of processing if you do your query through a temp
table as in my example.
This is a form of divide-and-conquer. By breaking a larger query like this
into smaller pieces, we can keep the internally created JOIN tables
smaller and you get back huge gains in performance because JOINS are, in
general, geometrically expensive (not linearly). A JOIN between two 1000
row tables requires the testing of 1000000 (1 million) possible conditions
(of course there are optimizations that can greatly reduce that value to
something more practical). IF you join three 1000 row tables, that results
in possibly 1000x1000x1000 = 1000000000 (1 billion) test conditions that
need to be evaluated. If we can pre-eliminate all but 10 rows from the
first table of that 3-JOIN query, we end up evaluating only 10x1000x1000
(10000000) 10 million test conditions and the query should (all other
things being equal) finish in 1/100th (1%) of the time the full, unbroken
query would have taken.
Make sense?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
