List:General Discussion« Previous MessageNext Message »
From:Greg Matthews Date:November 10 2002 11:03pm
Subject:Re: MySql 4.1 Sub Selects
View as plain text  
"Why don't you simply convert these queries to ordinary JOINs?"

...because we've already got a 90% finished application (75 tables/150 jsp
pages).

I was toying around with the idea last night of using a JOIN instead of
EXISTS but i'd have to change the way the persistence layer works.  I'm not
sure that it's a good idea to use joins (often requiring the DISTINCT
clause) instead of EXISTS -- seems like a "tail wagging the dog" strategy.
Isn't EXISTS a lot more efficient than an inner join?

We're going to offering the application on Oracle and so I wouldn't like to
de-optimize the SQL just so it works on mysql 4.0 if 4.1 will be out within
a few months.

Seems like I might be best to check again in a while and see how quickly you
guys have progressed with 4.1.

Cheers,
Greg.

----- Original Message -----
From: "Harald Fuchs" <lists-mysql@stripped>
To: <mysql@stripped>
Sent: Sunday, November 10, 2002 11:16 PM
Subject: Re: MySql 4.1 Sub Selects


> In article <014701c288a4$1b1bb550$0100a8c0@dev>,
> "Greg Matthews" <greg55@stripped> writes:
>
> > Your call. If a 4.1 binary was available then I'd spend time testing it
with
> > a J2EE app under JBoss on Windows XP -- otherwise i'll just have to
wait.
>
> > 95% of the subselects I do are simple correlated subqueries like this:
>
> > select <columns>
> > from tableA
> > where exists (
> >     select 1
> >     from tableB
> >     where tableB.myCol = tableA.myCol
> > )
>
> > or
>
> > select <columns> from tableA
> > where exists (
> >     select 1
> >     from tableB
> >     where tableB.myCol = tableA.myCol
> >     and tableB.someCol in ('A','B','C')
> > )
>
> Why don't you simply convert these queries to ordinary JOINs?
> Me thinks that's easier than experimenting with unoptimized pre-alpha
> code.
>
> [Filter fodder: SQL query]
>
> ---------------------------------------------------------------------
> 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 <mysql-thread124565@stripped>
> To unsubscribe, e-mail
<mysql-unsubscribe-greg55=ozemail.com.au@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>

Thread
MySql 4.1 Sub SelectsGreg Matthews10 Nov
  • Re: MySql 4.1 Sub SelectsJeremy Zawodny10 Nov
  • Re: MySql 4.1 Sub SelectsGreg Matthews10 Nov
    • Re: MySql 4.1 Sub SelectsJeremy Zawodny10 Nov
  • Re: MySql 4.1 Sub SelectsJocelyn Fournier10 Nov
  • Re: MySql 4.1 Sub SelectsGreg Matthews10 Nov
    • Re: MySql 4.1 Sub SelectsJeremy Zawodny10 Nov
  • Re: MySql 4.1 Sub SelectsHarald Fuchs10 Nov
  • Re: MySql 4.1 Sub SelectsGreg Matthews10 Nov
  • Re: MySql 4.1 Sub SelectsMichael T. Babcock11 Nov
  • Re: MySql 4.1 Sub SelectsGreg Matthews11 Nov
RE: MySql 4.1 Sub Selectsdaniel11 Nov
  • re: RE: MySql 4.1 Sub SelectsVictoria Reznichenko11 Nov
    • RE: RE: MySql 4.1 Sub SelectsDan Rossi11 Nov
      • Re: RE: MySql 4.1 Sub Selects and not stored proceduresGreg Matthews11 Nov
        • RE: RE: MySql 4.1 Sub Selects and not stored proceduresArthur Fuller11 Nov
          • Re: RE: MySql 4.1 Sub Selects and not stored proceduresGreg Matthews11 Nov
        • Re: MySql 4.1 Sub Selects and not stored proceduresDan Nelson11 Nov
        • Re: RE: MySql 4.1 Sub Selects and not stored proceduresJohn Ragan11 Nov
      • Re: MySql 4.1 Sub SelectsMichael T. Babcock11 Nov
re[2]: MySql 4.1 Sub SelectsRichard Morton11 Nov
  • Re: re[2]: MySql 4.1 Sub SelectsLenz Grimmer11 Nov