Try this one:
SELECT STRAIGHT_JOIN ArgumentCalendar.Docket
, ArgumentCalendar.Date
, DATE_FORMAT(ArgumentCalendar.Date, '%W, %M %e, %Y') AS formatted_date
, CaseID.CASEID
, CONCAT(Party1, ' v. ', Party2) AS name
, Blurb
FROM ArgumentCalendar
, CaseID
, Parties
LEFT JOIN Preview ON Preview.CASEID = Parties.CASEID
WHERE CaseID.DocketNumber = ArgumentCalendar.Docket
AND Parties.CASEID = CaseID.CASEID
AND '20010125' <= ArgumentCalendar.Date
On 25 Jan 2001 13:17:14 -0500, Brian Hughes wrote:
> Hi. I have a query which works and is quick, but it misses a couple of
> records.
>
> SELECT ArgumentCalendar.Docket
> , ArgumentCalendar.Date
> , CaseID.CASEID
> , CONCAT(Party1, ' v. ', Party2) AS name
> , Preview.Blurb
> FROM ArgumentCalendar
> , CaseID
> , Parties
> , Preview
> WHERE CaseID.DocketNumber = ArgumentCalendar.Docket
> AND Parties.CASEID = CaseID.CASEID
> AND Preview.CASEID = CaseID.CASEID
> AND '20001001' <= ArgumentCalendar.Date
> AND ArgumentCalendar.Date <= '20010630;
>
> I needed to _also_ select the records which don't have a corresponding
> record in Preview, so I threw in a left join:
>
> SELECT ArgumentCalendar.Docket
> , ArgumentCalendar.Date
> , DATE_FORMAT(ArgumentCalendar.Date, '%W, %M %e, %Y') AS formatted_date
> , CaseID.CASEID
> , CONCAT(Party1, ' v. ', Party2) AS name
> , Blurb
> FROM ArgumentCalendar
> , CaseID
> , Parties
> LEFT JOIN Preview ON Preview.CASEID = Parties.CASEID
> WHERE CaseID.DocketNumber = ArgumentCalendar.Docket
> AND Parties.CASEID = CaseID.CASEID
> AND '20010125' <= ArgumentCalendar.Date
>
> The second query works correctly with the left join, but now mysql looks at
> all 19000 records in Parties (according to EXPLAIN) and the query takes
> about 15 seconds to execute. I have fiddled and read & fiddled more, but I
> have failed to speed up this query. If I change the order around or add
> another left join I have removed the problem with the Parties table, but
> then EXPLAIN says all 19000 CaseID records are being examined. Without the
> LEFT JOIN MySQL only looks at all the ArgumentCalendar & all the Preview
> records, which are like 63 and 150 respectively.
>
> With the Left Join I select 61 records, without it 59 (which is as expected
> -- the issue is just speed).
>
> I am using MySQL 3.22.25.
>
> Am I screwed or is there some syntactic SQL point I am missing?
>
> - BLH
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
