Hi,
We are running numerous queries which join anything from 3 - 10 tables together.
Can I please ask the advice of the group as to the most efficient way to write such queries in MySQL V4. The example below has one 3 tables but 6/7 is more common.
If a condition can be specified either in the WHERE or HAVING clauses, it's preferable to place it in the WHERE clause so that it is subject to being optimized.
See: http://www.mysql.com/doc/en/SELECT.html
Which says:
The HAVING clause can refer to any column or alias named in the select_expression. It is applied nearly last, just before items are sent to the client, with no optimisation. (LIMIT is applied after HAVING.) Don't use HAVING for items that should be in the WHERE clause.
1. using the WHERE statement to show the matching columns in each table
SELECT tblnotetypes.strNoteType, Month([datTimestamp]) AS Expr1, Count(tblnotes.cntNotes) AS CountOfcntNotes, tblstaff.strFirstname, tblstaff.strSurname FROM tblnotes, tblnotetypes, tblstaff WHERE tblnotes.intNoteType = tblnotetypes.cntNoteType AND tblnotes.intAuthorStamp = tblstaff.cntStaffIndex AND tblnotetypes.strNoteType)='Enquiry' GROUP BY tblnotetypes.strNoteType, Month([datTimestamp]), tblstaff.strFirstname, tblstaff.strSurname
2. using the INNER JOIN type syntax combined with HAVING statement
SELECT tblnotetypes.strNoteType, Month([datTimestamp]) AS Expr1, Count(tblnotes.cntNotes) AS CountOfcntNotes, tblstaff.strFirstname, tblstaff.strSurname FROM (tblnotes INNER JOIN tblnotetypes ON tblnotes.intNoteType = tblnotetypes.cntNoteType) INNER JOIN tblstaff ON tblnotes.intAuthorStamp = tblstaff.cntStaffIndex GROUP BY tblnotetypes.strNoteType, Month([datTimestamp]), tblstaff.strFirstname, tblstaff.strSurname HAVING (((tblnotetypes.strNoteType)="Enquiry"));
Your comments would be greatly appreciated.
many thanks
-- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
