Do you have a question? Post it now! No Registration Necessary. Now with pictures!
November 24, 2005, 8:49 am
rate this thread
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,
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,
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!!
Re: SET SQL_BIG_SELECTS=1
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 "="
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.
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.
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
- » Re: mysql in a startup/shutdown loop at 30 sec intervals
- — Previous thread in » MySQL Database Forum
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum