SET SQL_BIG_SELECTS=1

[paul]

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.ProviderID, tblCourseInfo.OccupationID,
tblCourseInfo.AuthorityID, tblOccupations.Occupation,
tblOccupations.OccupationID, tblProviders.ProviderID,
tblProviders.ProvName, tblAuthorities.AuthorityID,
tblAuthorities.Authority
FROM tblCourseInfo, tblOccupations, tblProviders, tblAuthorities
WHERE tblCourseInfo.ProviderID LIKE 'varProvider'
AND tblCourseInfo.OccupationID LIKE 'varOccupation'
AND tblCourseInfo.AuthorityID LIKE 'varAuthority'
AND tblCourseInfo.OccupationID LIKE tblOccupations.OccupationID
AND tblCourseInfo.ProviderID LIKE tblProviders.ProviderID
AND tblCourseInfo.AuthorityID LIKE tblAuthorities.AuthorityID
ORDER BY tblOccupations.Occupation, tblProviders.ProvName,
tblAuthorities.Authority

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_SIZE=# 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!!

Many thanks
Suffrinmick

[Bill Karwin]

pa**@is-d.co.uk wrote:

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.ProviderID, tblCourseInfo.OccupationID,
tblCourseInfo.AuthorityID, tblOccupations.Occupation,
tblOccupations.OccupationID, tblProviders.ProviderID,
tblProviders.ProvName, tblAuthorities.AuthorityID,
tblAuthorities.Authority
FROM tblCourseInfo, tblOccupations, tblProviders, tblAuthorities
WHERE tblCourseInfo.ProviderID LIKE 'varProvider'
AND tblCourseInfo.OccupationID LIKE 'varOccupation'
AND tblCourseInfo.AuthorityID LIKE 'varAuthority'
AND tblCourseInfo.OccupationID LIKE tblOccupations.OccupationID
AND tblCourseInfo.ProviderID LIKE tblProviders.ProviderID
AND tblCourseInfo.AuthorityID LIKE tblAuthorities.AuthorityID
ORDER BY tblOccupations.Occupation, tblProviders.ProvName,
tblAuthorities.Authority
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
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_SIZE=# 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!!

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.