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
