At 01:19 PM 1/25/2001 -0800, Ryan Wahle wrote:
>Send the results of the EXPLAIN
table type possible_keys key key_len ref rows Extra
ArgumentCalendar ALL Date [none] [none] [none] 63 where used
CaseID ALL PRIMARY [none] [none] [none] 19701 where used
Parties eq_ref PRIMARY PRIMARY 4 CaseID.CASEID 1 [none]
Preview ALL [none] [none] [none] [none] 150 [none]
(blank cells indicated by [none])
- BLH
>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
