Hi. Thanks, I like that syntax, I didn't know I could put STRAIGHT_JOIN
there after the SELECT.
But it doesn't get me anything. EXPLAIN still says I am examining all
19,701 rows in CaseID: same problem as before.
- BLH
At 10:46 AM 1/25/2001 -0800, you wrote:
>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
---------------------------------------------------------------------
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
