PreCache's use of WHERE ... IN

[harryh]

Given something like Tip.findAll(By(Tip.venueid, 1111), PreCache
(Tip.userid))

The PreCache query param appears to do it's preaching by generating a
query that uses IN, like so:

SELECT DISTINCT users.id, users.firstname, users.lastname FROM users
WHERE id IN (SELECT userid FROM tips WHERE venueid = 1111 );

This is a bit concerting as I have found queries of this form to be
far less efficient (on a stock MySQL instance at least) than something
like this (which will return the same results):

SELECT DISTINCT users.id, users.firstname, users.lastname FROM users,
tips WHERE tips.userid = users.id AND tips.venueid = 1111;

Is there any particular reason that Lift is generating WHERE ... IN
queries? Is there some way to instruct Lift to do otherwise? If not,
should this possibly be changed?

This is somewhat related to my last post on this thread as it also
relates to WHERE ... IN queries.

http://groups.google.com/group/liftweb/browse_thread/thread/14d4a5aac48ac73d/

-harryh

[David Pollak]

On Tue, Aug 18, 2009 at 1:51 PM, harryh <har...@gmail.com> wrote:

Given something like Tip.findAll(By(Tip.venueid, 1111), PreCache
(Tip.userid))

The PreCache query param appears to do it's preaching by generating a
query that uses IN, like so:

SELECT DISTINCT users.id, users.firstname, users.lastname FROM users
WHERE id IN (SELECT userid FROM tips  WHERE venueid = 1111 );

This is a bit concerting as I have found queries of this form to be
far less efficient (on a stock MySQL instance at least) than something
like this (which will return the same results):

SELECT DISTINCT users.id, users.firstname, users.lastname FROM users,
tips WHERE tips.userid = users.id AND tips.venueid = 1111;

 

Is there any particular reason that Lift is generating WHERE ... IN
queries?  

Because making things line up name-wise for joins (especially when you're joining to the same table) is a lot more difficult.  All RDBMS except MySQL will optimize both inner queries and joins the same way.
 

Is there some way to instruct Lift to do otherwise?

No.
 

 If not,
should this possibly be changed?

It's not a high priority for me, but maybe on of the other committers wants to take up the project.
 

This is somewhat related to my last post on this thread as it also
relates to WHERE ... IN queries.

http://groups.google.com/group/liftweb/browse_thread/thread/14d4a5aac48ac73d/

-harryh

--
Lift, the simply functional web framework http://liftweb.net
Beginning Scala http://www.apress.com/book/view/1430219890
Follow me: http://twitter.com/dpp
Git some: http://github.com/dpp

[harryh]

> Because making things line up name-wise for joins (especially when you're
> joining to the same table) is a lot more difficult.  All RDBMS except MySQL
> will optimize both inner queries and joins the same way.

Interesting. I have been considering switching to PostgreSQL (for
reasons unrelated to this), and this issue may speed up my move. What
RDBMS are others generally using with Lift? Are there any general
recommendations?

-harryh

[David Pollak]

I use PostgreSQL for all my production sites.  It's amazingly solid.
 

-harryh

[Jeppe Nejsum Madsen]

David Pollak <feeder.of...@gmail.com> writes:

> On Tue, Aug 18, 2009 at 3:24 PM, harryh <har...@gmail.com> wrote:
>
>>
>> > Because making things line up name-wise for joins (especially when you're
>> > joining to the same table) is a lot more difficult. All RDBMS except
>> MySQL
>> > will optimize both inner queries and joins the same way.
>>
>> Interesting. I have been considering switching to PostgreSQL (for
>> reasons unrelated to this), and this issue may speed up my move. What
>> RDBMS are others generally using with Lift? Are there any general
>> recommendations?
>
>
> I use PostgreSQL for all my production sites. It's amazingly solid.

+1

/Jeppe

[Derek Chen-Becker]

And PostGIS rocks if you do any GIS-related stuff.

[harryh]

> And PostGIS rocks if you do any GIS-related stuff.

Which I am (this was my other reason for planning on migrating over).
I guess it's time to take the plunge.

-harryh