SET SQL_BIG_SELECTS=1

**Bill Karwin** · Nov 24 '05, 08:55 PM

Re: SET SQL_BIG_SELECTS =1

paul@is-d.co.uk wrote:[color=blue]
> Hi Everyone
>
> I've had a database query page running on a site for a few months:
> (http://www.learningdirectory.info/search.php) with no problems.
>
> The search page contains the following query:
>
> SELECT CourseID, tblCourseInfo.P roviderID, tblCourseInfo.O ccupationID,
> tblCourseInfo.A uthorityID, tblOccupations. Occupation,
> tblOccupations. OccupationID, tblProviders.Pr oviderID,
> tblProviders.Pr ovName, tblAuthorities. AuthorityID,
> tblAuthorities. Authority
> FROM tblCourseInfo, tblOccupations, tblProviders, tblAuthorities
> WHERE tblCourseInfo.P roviderID LIKE 'varProvider'
> AND tblCourseInfo.O ccupationID LIKE 'varOccupation'
> AND tblCourseInfo.A uthorityID LIKE 'varAuthority'
> AND tblCourseInfo.O ccupationID LIKE tblOccupations. OccupationID
> AND tblCourseInfo.P roviderID LIKE tblProviders.Pr oviderID
> AND tblCourseInfo.A uthorityID LIKE tblAuthorities. AuthorityID
> ORDER BY tblOccupations. Occupation, tblProviders.Pr ovName,
> tblAuthorities. Authority[/color]

This is not related to your question about SET, but I notice that you're
using the "LIKE" predicate where you could more efficiently use the "="
equality operator.

You can also use aliases to reduce having to retype the table names, and
I think using JOIN syntax is more clear than putting join conditions in
the WHERE clause.

Here is how I would write this query:

SELECT C.CourseID, O.Occupation, O.OccupationID, P.ProviderID,
P.ProvName, A.AuthorityID, A.Authority
FROM tblCourseInfo AS C
INNER JOIN tblOccupations AS O ON C.OccupationID = O.OccupationID
INNER JOIN tblProviders AS P ON C.ProviderID = P.ProviderID
INNER JOIN tblAuthorities AS A ON C.AuthorityID = A.AuthorityID
WHERE C.ProviderID = 'varProvider'
AND C.OccupationID = 'varOccupation'
AND C.AuthorityID = 'varAuthority'
ORDER BY O.Occupation, P.ProvName, A.Authority

Using "=" instead of "LIKE" should run a bit faster. "LIKE" is a
pattern-matching function, not a string equality function. Use "LIKE"
when you need wildcards in the comparison.
See http://dev.mysql.com/doc/refman/5.0/...functions.html
[color=blue]
> But in the last few days a message has appeared:
> 'The SELECT would examine more rows than MAX_JOIN_SIZE. Check your
> WHERE and use SET SQL_BIG_SELECTS =1 or SET SQL_MAX_JOIN_SI ZE=# if the
> SELECT is ok'
>
> I understand that I need to add ''SET SQL_BIG_SELECTS =1' but could
> someone please tell me where or how I add it? I haven't got a clue!![/color]

It's a separate statement, which you can execute at any time before you
execute your SELECT. The setting remains in effect during the current
database session between your PHP application and the database.
See http://dev.mysql.com/doc/refman/5.0/en/set-option.html

For example:

SET SESSION SQL_BIG_SELECTS =1;
SELECT C.CourseID, ... <the rest of the query>;

You can also set this variable permanently for all sessions by adding it
to an options file on your MySQL server. See
http://dev.mysql.com/doc/refman/5.0/...ion-files.html.

Regards,
Bill K.

SET SQL_BIG_SELECTS=1

SET SQL_BIG_SELECTS=1

Comment