I have the following SQL:

SELECT p.PartNo, p.MainGroup
FROM Parts p
WHERE p.PartNo in (SELECT PartNo FROM GroupParts where GroupID = 11)

This SQL uses a row scan in Parts for the PartNo condition. PartNo is the Primary Key of that table and it should be used in this case.

Writing the following SQL does use the index, but will not work in my case as the sub select will return more than one row:
SELECT p.PartNo, p.MainGroup
FROM Parts p
WHERE p.PartNo = (SELECT PartNo FROM GroupParts where GroupID = 11 and PartNo = 'xy')

Also this works correct and uses the index:
SELECT p.PartNo, p.MainGroup
FROM Parts p
WHERE p.PartNo in ('XX', 'XY', 'ZZ', 'ZY')

Rolf


When posting something like this it would help to post the execution plan as well.

My take on it would be you're complaining about this

p.PartNo in (SELECT PartNo FROM GroupParts where GroupID = 11)

not using an index scan. I think its because the subselect isn't guaranteed to produce the same result for each row in Parts so each PartNo has to be checked in turn against the subselect

<<Writing the following SQL does use the index, but will not work in my case as the sub select will return more than one row:>>

Without seeing the execution plan I don't know. I have a guess but it's probably wrong

<<Also this works correct and uses the index:>>

You've given it a constant list so it can be flipped and the items in the list checked against the index - again a guess.

Subselects are great for many things but I'd be tempted to use a JOIN for something like this eg

SELECT p.PartNo, p.MainGroup
FROM Parts p
JOIN GroupParts g ON g.PartNo = p.PartNo
WHERE g.GroupID = 11

probably wrong but should give you a starter


Roy Lambert

Roy,

<<I think its because the subselect isn't guaranteed to produce the same result for each row in Parts so each PartNo has to be checked in turn against the subselect>>

This makes only sense, if the driver table (parts) is part of the sub select statement. In my case it is fix and doesn't have any relation to the driver table. In such a situations, the optimizer should better optimize this type of sub select.

<<Subselects are great for many things but I'd be tempted to use a JOIN for something like this eg>>

I know that a JOIN will work, but I'm need a sensitive result and as such a JOIN will not work.

Regards
Rolf


This is the Execution Plan:
================================================================================
SQL Query (Executed by ElevateDB 2.10 Build 1)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.
================================================================================

SELECT ALL
"p"."PartNo" AS "PartNo",
"p"."MainGroup" AS "MainGroup"
FROM "Parts" AS "p"
WHERE "p"."PartNo" IN (SELECT ALL "PartNo" AS "PartNo" FROM "GroupParts" WHERE
"GroupID" = 4 ORDER BY "PartNo")

Source Tables
-------------

Parts (p): 39865 rows

Result Set
----------

The result set was sensitive
The result set consisted of zero or more rows
The result set was ordered using the index PrimaryKey

Filtering
---------

The following filter condition was applied to the Parts (p) table:

"p"."PartNo" IN (SELECT ALL "PartNo" AS "PartNo" FROM "GroupParts" WHERE
"GroupID" = 4

ORDER BY "PartNo")

Row scan (Parts): 39865 rows, 19773040 bytes estimated cost

================================================================================
10 row(s) returned in 1.89 secs
================================================================================

Rolf,

<< This SQL uses a row scan in Parts for the PartNo condition. PartNo is the Primary Key of that table and it should be used in this case. >>

Yes, but that's *not* the row scan that EDB is referring to.  It's referring to the fact that the *outer* query has no WHERE clause and will need to scan *all* of the rows in the Parts table in order to evaluate the WHERE clause.  There's simply no way around this other than converting the WHERE clause to using a JOIN instead.

Tim Young
Elevate Software
www.elevatesoft.com

Rolf


I had a similar requirement. In my case I wanted to apply a filter to a table so the user could edit it. I adopted a two stage strategy.

Stage 1: generate a list of record keys for the records I wanted
Stage 2: use those in a WHERE .. IN .. statement

The LIST keyword makes it a bit easier, and it should be possible to make it into a script (mine just used Delphi and TStringList). It might be faster than the straighforward subselect.

Roy Lambert

>>Tim Young [Elevate Software] wrote:

Yes, but that's *not* the row scan that EDB is referring to.  It's referring to the fact that the *outer* query has no WHERE clause and will need to scan *all* of the rows in the Parts table in order to evaluate the WHERE clause.  There's simply no way around this other than converting the WHERE clause to using a JOIN instead.<<

But why can other DBMS use the Key here? MySQL had the same issue until Version 5.6. After that it was fixed and it uses now the index in this cases. Why can't EDB do the same? It's clear to me, that this will not be a simple task, as the optiomizer must internally rewrite this type of SQL.

So I just wondering, why can MySQL do it, as EDB isn't able to do this? This limitiation will make the sensitive aproach near unusable and we are forced to use some type of ugly Master/Detail to get an editable query.

Regards
Rolf

Rolf,

<< So I just wondering, why can MySQL do it, as EDB isn't able to do this? This limitiation will make the sensitive aproach near unusable and we are forced to use some type of ugly Master/Detail to get an editable query. >>

You're really asking me this ?  I'm one guy, Rolf - there are certain things that I can do and certain things that are outside the amount of time I have available as a sole human being, whereas MySQL is backed by Oracle, one of the largest software companies on the planet.

The solution is to rewrite the query as a join and use it with a CREATE TEMPORARY TABLE AS SELECT to generate a temporary table.  Then you can use the dataset events to post any modifications back to the original tables.  This will get easier at some point in the future when I introduce INSTEAD OF triggers, which allow you to edit insensitive views, but for now that is what you'll need to do.

Tim Young
Elevate Software
www.elevatesoft.com

As an ex one-man-band (I'm retired as of last November) I have to totally support Tim on this.

Roy Lambert

Tim, Roy,

I fully understand your point and I will live with it, but I'm a little bit disappointed, that this is not working. But hey, I'm on your boat too. I'm also more or less a one man shop.

I will/must live with it for now.

Regards
Rolf