Send the results of the EXPLAIN
On 25 Jan 2001 15:15:35 -0500, Brian Hughes wrote:
> 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
