I was actually just trying to help someone; this isn't my question. :-)
You may be right, but we really don't know until the guy that posted
this tries it out. I recommended straight_join because two weeks ago I
was having a problem with the join where mysql just didn't seem to
optimize it correctly and it took about 3 seconds to run, where when I
took out a certain table in the join it ran in like 0.14 seconds. I
tried every different possible way and it still ran the same 3 seconds,
but when I added the straight_join and put the tables in the order I
wanted to select from first, then second, then third, it went to like
0.35 seconds which was very good.
On 25 Jan 2001 22:30:03 +0300, Artem Koutchine wrote:
> Hello!
>
> While STRAIGHT_JOIN might help, in my personal
> experience optimizer usually does a good job.
>
> I think the problem is in here:
>
> FROM ArgumentCalendar
> > , CaseID
> > , Parties
> > LEFT JOIN Preview ON Preview.CASEID = Parties.CASEID
>
> strictly speaking in "ArgumentCalendar, CaseID, Parties" which
> is IMHO the mother of all evil in relational dtabase. Each comma
> means cartesian product of two table and the cartesian product
> with the third table. It *usually* take about forever on real database
> to complete. I'd recommend put LEFT JOIN first and then do
> cartesian multiplications. And, just i case, though in the
> STRAIGHT_JOIN
>
> So, the query *MIGHT* look like:
>
> 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 LEFT JOIN CaseID on CaseID.DocketNumber =
> ArgumentCalendar.Docket
> LEFT JOIN Parties.CASEID = CaseID.CASEID
> LEFT JOIN Preview ON Preview.CASEID = Parties.CASEID
> WHERE
> '20010125' <= ArgumentCalendar.Date AND
> CaseID.DocketNumber IS NOT NULL AND
> Parties.CASEID IS NOT NULL
>
> Some of left joins might also be INNER
>
> I might be wrong, but it always helps me. The other thing is the indices you
> are using and number
> of actual records. I need to table definitions and explain for your selects
> and for this select to
> come up with some solution.
> Here we are having databases with over 20 tables where 5 of them have more
> than 300000 records
> and we are getting LEFT JOIN and GROUP BY select for all 300000 records in
> less than 20 seconds.
>
> One more thing: you might want to increase your key case and sort buffer to
> make it quicker.
>
> Regards,
> Artem
>
> ----- Original Message -----
> From: "Ryan Wahle" <[EMAIL PROTECTED]>
> To: "Brian Hughes" <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Thursday, January 25, 2001 9:46 PM
> Subject: Re: I am failing to optimize this left join ...
>
>
> >
> > 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
