Two UNION's and an INTERSECT?
A user on IDS 7.3 had a slow query (~8s, run ~30 times per batch) selecting users that have one flag but not others, built from several IN/NOT IN subqueries against a 600K-row user_flags table; with no INTERSECT available in 7.3 he asked how to speed it up. Suggestions: use temp tables to pre-collect matching user_ids; and (Jonathan Leffler) reformat/simplify — drop the duplicated subquery, merge the NOT IN subqueries with OR, drop the redundant outer table, and turn the positive IN subquery into a join, possibly trying NOT EXISTS. The poster said the rewrite only worked for that specific case and actually raised the optimizer cost from 84K to 120K. No working resolution is recorded; the thread ends with doubts about the schema design and a request for the explain plan.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello all:
My DB's are purring along nicely, thanks to some help from this group
over the last few years; but I have a stumper, so I thought I'd see if
anyone has some novel ideas.
I have three tables involved .... for instance:
userData:
id serial primary key,
dateCreated date,
name varchar(100),
mileId foreign key (references mileData(id))
mileData:
id serial primary key,
description varchar(100)
flagData:
id serial primary key,
description varchar(100)
user_flags:
id serial primary key,
user_id foreign key (references userData(id)),
flag_id foreign key (references flagData(id))
There are roughly 8 rows in flagData, 100 in mileData, and roughly
200,000 in userData. As it's a 1->Many from userData to user_flags,
1->1 from userData to mileData and a Many->1 from user_flags to
flagData ... and user_flags has more than 600K rows.
That being said, I need to locate some certain number of user_id's in
the following mannger:
SELECT FIRST 200 user_id, dateCreated FROM user_flags, userData WHEREuser_id = userData.id AND (user_id IN (select user_id FROM user_flags
WHERE flag_id = 3) AND user_id NOT IN (select user_id FROM user_flags
WHERE flag_id = 1)) AND inv_id NOT IN (select user_id FROM user_flags
WHERE flag_id = 5) AND inv_id NOT IN (select user_id FROM user_flags
WHERE flag_id = 1) AND mileId = 50 ORDER BY dateCreated DESC;
I've created Index's on datecreated, and a joint index on user_id and
flag_id IN user_flags. I can't really hard-code any of it very well,
as the numerical values for flag_id, mileId, and FIRST are generated
on the fly from a Java app. The above query works ... but it's SLOW.
It takes more than 8 seconds to run. Unfortunately, it runs
approximately 30 times per run (with different user-input data), and
then has to process each of the 30 queries ... which means it's
running for more than 4 minutes -- and that's when I have exclusive
use of the db. I've seen it take more than 20 minutes.
My goal is to get this to run as fast as possible. From what I can
tell, it's really the intersection of two unions; but my set theory
may be off. I even whipped out and dusted the database theory books
off; no luck.
I'm using IDS 7.3 .... so I don't have intersect .... any ideas; any
onstat values that would help me get this ticking along faster?
Thanks.
--Anthony
Anthony Presley wrote:
> My DB's are purring along nicely, thanks to some help from this group
> over the last few years; but I have a stumper, so I thought I'd see if
> anyone has some novel ideas.
>
> I have three tables involved .... for instance:
>
> userData:
> id serial primary key,
> dateCreated date,
> name varchar(100),
> mileId foreign key (references mileData(id))
>
> mileData:
> id serial primary key,
> description varchar(100)
>
> flagData:
> id serial primary key,
> description varchar(100)
>
> user_flags:
> id serial primary key,
> user_id foreign key (references userData(id)),
> flag_id foreign key (references flagData(id))
>
> There are roughly 8 rows in flagData, 100 in mileData, and roughly
> 200,000 in userData. As it's a 1->Many from userData to user_flags,
> 1->1 from userData to mileData and a Many->1 from user_flags to
> flagData ... and user_flags has more than 600K rows.
>
> That being said, I need to locate some certain number of user_id's in
> the following mannger:
>
> SELECT FIRST 200 user_id, dateCreated FROM user_flags, userData WHERE> user_id = userData.id AND (user_id IN (select user_id FROM user_flags
> WHERE flag_id = 3) AND user_id NOT IN (select user_id FROM user_flags
> WHERE flag_id = 1)) AND inv_id NOT IN (select user_id FROM user_flags
> WHERE flag_id = 5) AND inv_id NOT IN (select user_id FROM user_flags
> WHERE flag_id = 1) AND mileId = 50 ORDER BY dateCreated DESC;
Which table has inv_id in it? Is that indexed? How does inv_id relate to
user_id?
How about using temp tables: select all the user_ids that match the
subselects into a temp table and then join?
> I've created Index's on datecreated, and a joint index on user_id and
> flag_id IN user_flags. I can't really hard-code any of it very well,
> as the numerical values for flag_id, mileId, and FIRST are generated
> on the fly from a Java app. The above query works ... but it's SLOW.
> It takes more than 8 seconds to run. Unfortunately, it runs
> approximately 30 times per run (with different user-input data), and
> then has to process each of the 30 queries ... which means it's
> running for more than 4 minutes -- and that's when I have exclusive
> use of the db. I've seen it take more than 20 minutes.
>
> My goal is to get this to run as fast as possible. From what I can
> tell, it's really the intersection of two unions; but my set theory
> may be off. I even whipped out and dusted the database theory books
> off; no luck.
>
> I'm using IDS 7.3 .... so I don't have intersect .... any ideas; any
> onstat values that would help me get this ticking along faster?
--
Ciao,
The Obnoxious One
"Ogni uomo mi guarda come se fossi una testa di cazzo"
Obnoxio The Clown <obnoxio@hotmail.com> wrote in message news:<bp4pvk$1jlkep$1@ID-64669.news.uni-berlin.de>...
> Anthony Presley wrote:
>
> > My DB's are purring along nicely, thanks to some help from this group
> > over the last few years; but I have a stumper, so I thought I'd see if
> > anyone has some novel ideas.
> >
> > I have three tables involved .... for instance:
> >
> > userData:
> > id serial primary key,
> > dateCreated date,
> > name varchar(100),
> > mileId foreign key (references mileData(id))
> >
> > mileData:
> > id serial primary key,
> > description varchar(100)
> >
> > flagData:
> > id serial primary key,
> > description varchar(100)
> >
> > user_flags:
> > id serial primary key,
> > user_id foreign key (references userData(id)),
> > flag_id foreign key (references flagData(id))
> >
> > There are roughly 8 rows in flagData, 100 in mileData, and roughly
> > 200,000 in userData. As it's a 1->Many from userData to user_flags,
> > 1->1 from userData to mileData and a Many->1 from user_flags to
> > flagData ... and user_flags has more than 600K rows.
> >
> > That being said, I need to locate some certain number of user_id's in
> > the following mannger:
> >
> > SELECT FIRST 200 user_id, dateCreated FROM user_flags, userData WHERE> > user_id = userData.id AND (user_id IN (select user_id FROM user_flags
> > WHERE flag_id = 3) AND user_id NOT IN (select user_id FROM user_flags
> > WHERE flag_id = 1)) AND inv_id NOT IN (select user_id FROM user_flags
> > WHERE flag_id = 5) AND inv_id NOT IN (select user_id FROM user_flags
> > WHERE flag_id = 1) AND mileId = 50 ORDER BY dateCreated DESC;
>
> Which table has inv_id in it? Is that indexed? How does inv_id relate to
> user_id?
My fault .... cutting pasting parts of a query .... inv_id should read
user_id. IE, the query is (assuming I don't have any typos):
SELECT FIRST 200 user_id, dateCreated FROM user_flags, userData WHEREuser_id = userData.id AND (user_id IN (select user_id FROM user_flags
WHERE flag_id = 3) AND user_id NOT IN (select user_id FROM user_flags
WHERE flag_id = 1)) AND user_id NOT IN (select user_id FROM user_flags
WHERE flag_id = 5) AND user_id NOT IN (select user_id FROM user_flags
WHERE flag_id = 1) AND mileId = 50 ORDER BY dateCreated DESC;
> How about using temp tables: select all the user_ids that match the
> subselects into a temp table and then join?
That would be possible .... but how would you recommend implementing
it? From what I can tell, the NOT IN is what is causing it to take a
long time.
> > I've created Index's on datecreated, and a joint index on user_id and
> > flag_id IN user_flags. I can't really hard-code any of it very well,
> > as the numerical values for flag_id, mileId, and FIRST are generated
> > on the fly from a Java app. The above query works ... but it's SLOW.
> > It takes more than 8 seconds to run. Unfortunately, it runs
> > approximately 30 times per run (with different user-input data), and
> > then has to process each of the 30 queries ... which means it's
> > running for more than 4 minutes -- and that's when I have exclusive
> > use of the db. I've seen it take more than 20 minutes.
> >
> > My goal is to get this to run as fast as possible. From what I can
> > tell, it's really the intersection of two unions; but my set theory
> > may be off. I even whipped out and dusted the database theory books
> > off; no luck.
> >
> > I'm using IDS 7.3 .... so I don't have intersect .... any ideas; any
> > onstat values that would help me get this ticking along faster?
Anthony Presley wrote:
> Obnoxio The Clown <obnoxio@hotmail.com> wrote:
>>Anthony Presley wrote:
>>>My DB's are purring along nicely, thanks to some help from this group
>>>over the last few years; but I have a stumper, so I thought I'd see if
>>>anyone has some novel ideas.
>>>
>>>I have three tables involved .... for instance:
>>>
>>>userData:
>>> id serial primary key,
>>> dateCreated date,
>>> name varchar(100),
>>> mileId foreign key (references mileData(id))
>>>
>>>mileData:
>>> id serial primary key,
>>> description varchar(100)
>>>
>>>flagData:
>>> id serial primary key,
>>> description varchar(100)
>>>
>>>user_flags:
>>> id serial primary key,
>>> user_id foreign key (references userData(id)),
>>> flag_id foreign key (references flagData(id))
I think the id column is probably an abuse of primary keys and a waste
of space - your real primary key is the combination of user_id and
flag_id (and it would be at best wasteful and at worst a downright
error to have two rows in the table with different id values and the
same user_id and flag_id). One clue that this might be bad - you
don't use the id column?
>>>There are roughly 8 rows in flagData, 100 in mileData, and roughly
>>>200,000 in userData. As it's a 1->Many from userData to user_flags,
>>>1->1 from userData to mileData and a Many->1 from user_flags to
>>>flagData ... and user_flags has more than 600K rows.
>>>
>>>That being said, I need to locate some certain number of user_id's in
>>>the following mannger:
>>>
>>>SELECT FIRST 200 user_id, dateCreated FROM user_flags, userData WHERE>>>user_id = userData.id AND (user_id IN (select user_id FROM user_flags
>>>WHERE flag_id = 3) AND user_id NOT IN (select user_id FROM user_flags
>>>WHERE flag_id = 1)) AND inv_id NOT IN (select user_id FROM user_flags
>>>WHERE flag_id = 5) AND inv_id NOT IN (select user_id FROM user_flags
>>>WHERE flag_id = 1) AND mileId = 50 ORDER BY dateCreated DESC;
>>
>>Which table has inv_id in it? Is that indexed? How does inv_id relate to
>>user_id?
>
>
> My fault .... cutting pasting parts of a query .... inv_id should read
> user_id. IE, the query is (assuming I don't have any typos):
>
> SELECT FIRST 200 user_id, dateCreated FROM user_flags, userData WHERE> user_id = userData.id AND (user_id IN (select user_id FROM user_flags
> WHERE flag_id = 3) AND user_id NOT IN (select user_id FROM user_flags
> WHERE flag_id = 1)) AND user_id NOT IN (select user_id FROM user_flags
> WHERE flag_id = 5) AND user_id NOT IN (select user_id FROM user_flags
> WHERE flag_id = 1) AND mileId = 50 ORDER BY dateCreated DESC;
You mean 'the query is unreadable'?
SELECT FIRST 200 user_id, dateCreated
FROM user_flags, userData
WHERE user_id = userData.id
AND (user_id IN
(SELECT user_id FROM user_flags WHERE flag_id = 3)
AND user_id NOT IN
(SELECT user_id FROM user_flags WHERE flag_id = 1))
AND user_id NOT IN
(SELECT user_id FROM user_flags WHERE flag_id = 5)
AND user_id NOT IN
(SELECT user_id FROM user_flags WHERE flag_id = 1)
AND mileId = 50
ORDER BY dateCreated DESC;
It is now semi-comprehensible - the original formatting completely
loses the substructure. Except that Mozilla wraps the lines, I'd have
flattened the sub-queries so they use one per line.
Can the last pair of sub-queries be combined into:
AND user_id NOT IN (SELECT user_id FROM user_flags WHERE flag_id = 1
OR flag_id = 5)
Also, more significantly, the second and last sub-queries are the
same, so one of those is irrelevant - the parentheses don't really
alter the meaning.
I also think you you can probably speed things up by using the user_id
in the user_flags table in conjunction with the user_id from the
uuserdata table (why the difference in naming convention - underscore
separating words vs no underscore?). Let's use table aliases
consistently - it makes it clearer what's going on:
SELECT FIRST 200 f.user_id, d.dateCreated
FROM user_flags f, userData d
WHERE f.user_id = d.id
AND f.user_id IN
(SELECT f1.user_id FROM user_flags f1 WHERE f1.flag_id = 3)
AND f.user_id NOT IN
(SELECT f2.user_id FROM user_flags f2
WHERE f2.flag_id = 5 OR f2.flag_id = 1)
AND d.mileId = 50
ORDER BY dateCreated DESC;
The tags on f1.user_id, f2.user_id and f3.user_id are
semi-speculative; to my mind, they are at best confusing because the
sub-query could, in one version of theory, refer to the table aliassed
as f instead of the numbered f. However, I think the 'tighter
binding' rules mean my annotation is likely correct.
The table f in the outer query is redundant. You can simplify this
again by writing:
SELECT FIRST 200 d.id AS user_id, d.dateCreated
FROM userData d
WHERE d.id IN
(SELECT f1.user_id FROM user_flags f1 WHERE f1.flag_id = 3)
AND d.id NOT IN
(SELECT f2.user_id FROM user_flags f2
WHERE f2.flag_id = 5 OR f2.flag_id = 1)
AND d.mileId = 50
ORDER BY dateCreated DESC;
Now the sub-queries return all the possible user_id values where the
flag_id does (or does not) have the relevant values. The first
sub-query can be converted to a join again, considerably improving the
probable performance:
SELECT FIRST 200 d.id AS user_id, d.dateCreated
FROM userData d, user_flags f1
WHERE d.id = f1.user_id
AND f1.flag_id = 3
AND d.id NOT IN
(SELECT f2.user_id FROM user_flags f2
WHERE f2.flag_id = 5 OR f2.flag_id = 1)
AND d.mileId = 50
ORDER BY dateCreated DESC;
This is quite a bit simpler than the original query. You'll have to
validate it against your database, but I think there's a moderate
chance the transforms are correct.
What gets more debatable is whether it is worth trying to modify the
NOT IN clause. You could easily convert it into a correlated
sub-query, or a NOT EXISTS correlated sub-query, but I'm not sure that
would be a performance enhancement - experimentation would be needed
to see what the costs are.
>>How about using temp tables: select all the user_ids that match the
>>subselects into a temp table and then join?
>
>
> That would be possible .... but how would you recommend implementing
> it? From what I can tell, the NOT IN is what is causing it to take a
> long time.
>
>
>>>I've created Index's on datecreated, and a joint index on user_id and
>>>flag_id IN user_flags. I can't really hard-code any of it very well,
>>>as the numerical values for flag_id, mileId, and FIRST are generated
>>>on the fly from a Java app. The above query works ... but it's SLOW.
>>>It takes more than 8 seconds to run. Unfortunately, it runs
>>>approximately 30 times per run (with different user-input data), and
>>>then has to process each of the 30 queries ... which means it's
>>>running for more than 4 minutes -- and that's when I have exclusive
>>>use of the db. I've seen it take more than 20 minutes.
>>>
>
Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<bUttb.1339$sb4.655@newsread2.news.pas.earthlink.net>...
[SNIP]
> >>>userData:
> >>> id serial primary key,
> >>> dateCreated date,
> >>> name varchar(100),
> >>> mileId foreign key (references mileData(id))
> >>>
> >>>mileData:
> >>> id serial primary key,
> >>> description varchar(100)
> >>>
> >>>flagData:
> >>> id serial primary key,
> >>> description varchar(100)
> >>>
> >>>user_flags:
> >>> id serial primary key,
> >>> user_id foreign key (references userData(id)),
> >>> flag_id foreign key (references flagData(id))
>
>
> I think the id column is probably an abuse of primary keys and a waste
> of space - your real primary key is the combination of user_id and
> flag_id (and it would be at best wasteful and at worst a downright
> error to have two rows in the table with different id values and the
> same user_id and flag_id). One clue that this might be bad - you
> don't use the id column?
Hmm ... well, yes, I do use the id key. See, a user may have multiple
flags, not unique. When editing in an interactive application, yes,
the id is used. Probably more frequently than the [indexed]
combination (user_id, flag_id) combination.
> >>>There are roughly 8 rows in flagData, 100 in mileData, and roughly
> >>>200,000 in userData. As it's a 1->Many from userData to user_flags,
> >>>1->1 from userData to mileData and a Many->1 from user_flags to
> >>>flagData ... and user_flags has more than 600K rows.
> >>>
> >>>That being said, I need to locate some certain number of user_id's in
> >>>the following mannger:
> >>>
> >>>SELECT FIRST 200 user_id, dateCreated FROM user_flags, userData WHERE> >>>user_id = userData.id AND (user_id IN (select user_id FROM user_flags
> >>>WHERE flag_id = 3) AND user_id NOT IN (select user_id FROM user_flags
> >>>WHERE flag_id = 1)) AND inv_id NOT IN (select user_id FROM user_flags
> >>>WHERE flag_id = 5) AND inv_id NOT IN (select user_id FROM user_flags
> >>>WHERE flag_id = 1) AND mileId = 50 ORDER BY dateCreated DESC;
> >>
> >>Which table has inv_id in it? Is that indexed? How does inv_id relate to
> >>user_id?
> >
> >
> > My fault .... cutting pasting parts of a query .... inv_id should read
> > user_id. IE, the query is (assuming I don't have any typos):
> >
> > SELECT FIRST 200 user_id, dateCreated FROM user_flags, userData WHERE> > user_id = userData.id AND (user_id IN (select user_id FROM user_flags
> > WHERE flag_id = 3) AND user_id NOT IN (select user_id FROM user_flags
> > WHERE flag_id = 1)) AND user_id NOT IN (select user_id FROM user_flags
> > WHERE flag_id = 5) AND user_id NOT IN (select user_id FROM user_flags
> > WHERE flag_id = 1) AND mileId = 50 ORDER BY dateCreated DESC;
>
> You mean 'the query is unreadable'?
Six one way .... half a dozen the other .....
> SELECT FIRST 200 user_id, dateCreated
> FROM user_flags, userData
> WHERE user_id = userData.id
> AND (user_id IN
> (SELECT user_id FROM user_flags WHERE flag_id = 3)
> AND user_id NOT IN
> (SELECT user_id FROM user_flags WHERE flag_id = 1))
> AND user_id NOT IN
> (SELECT user_id FROM user_flags WHERE flag_id = 5)
> AND user_id NOT IN
> (SELECT user_id FROM user_flags WHERE flag_id = 1)
> AND mileId = 50
> ORDER BY dateCreated DESC;>
> It is now semi-comprehensible - the original formatting completely
> loses the substructure. Except that Mozilla wraps the lines, I'd have
> flattened the sub-queries so they use one per line.
>
> Can the last pair of sub-queries be combined into:
>
> AND user_id NOT IN (SELECT user_id FROM user_flags WHERE flag_id = 1
> OR flag_id = 5)
It can.
> Also, more significantly, the second and last sub-queries are the
> same, so one of those is irrelevant - the parentheses don't really
> alter the meaning.
Right ... but they won't always be. More often than not, they won't
be the same.
> I also think you you can probably speed things up by using the user_id
> in the user_flags table in conjunction with the user_id from the
> uuserdata table (why the difference in naming convention - underscore
> separating words vs no underscore?).
No reason ... it's consistent in the db, just not here.
> Let's use table aliases consistently - it makes it clearer what's going on:
>
> SELECT FIRST 200 f.user_id, d.dateCreated
> FROM user_flags f, userData d
> WHERE f.user_id = d.id
> AND f.user_id IN
> (SELECT f1.user_id FROM user_flags f1 WHERE f1.flag_id = 3)
> AND f.user_id NOT IN
> (SELECT f2.user_id FROM user_flags f2
> WHERE f2.flag_id = 5 OR f2.flag_id = 1)
> AND d.mileId = 50
> ORDER BY dateCreated DESC;>
> The tags on f1.user_id, f2.user_id and f3.user_id are
> semi-speculative; to my mind, they are at best confusing because the
> sub-query could, in one version of theory, refer to the table aliassed
> as f instead of the numbered f. However, I think the 'tighter
> binding' rules mean my annotation is likely correct.
>
> The table f in the outer query is redundant. You can simplify this
> again by writing:
>
> SELECT FIRST 200 d.id AS user_id, d.dateCreated
> FROM userData d
> WHERE d.id IN
> (SELECT f1.user_id FROM user_flags f1 WHERE f1.flag_id = 3)
> AND d.id NOT IN
> (SELECT f2.user_id FROM user_flags f2
> WHERE f2.flag_id = 5 OR f2.flag_id = 1)
> AND d.mileId = 50
> ORDER BY dateCreated DESC;>
> Now the sub-queries return all the possible user_id values where the
> flag_id does (or does not) have the relevant values. The first
> sub-query can be converted to a join again, considerably improving the
> probable performance:
>
> SELECT FIRST 200 d.id AS user_id, d.dateCreated
> FROM userData d, user_flags f1
> WHERE d.id = f1.user_id
> AND f1.flag_id = 3
> AND d.id NOT IN
> (SELECT f2.user_id FROM user_flags f2
> WHERE f2.flag_id = 5 OR f2.flag_id = 1)
> AND d.mileId = 50
> ORDER BY dateCreated DESC;>
> This is quite a bit simpler than the original query. You'll have to
> validate it against your database, but I think there's a moderate
> chance the transforms are correct.
:-) Here's where Informix starts to confuse me, but, maybe my db
theory is off. I have been able to deduce a similar query using the
method you went through, although it will only work in this specific
case [mine above, while unreadable, works in all of my cases].
HOWEVER .... the cost has now gone from 84K to 120K. IE, the query
that you've deduced is almost 2X as slow.
> What gets more debatable is whether it is worth trying to modify the
> NOT IN clause. You could easily convert it into a correlated
> sub-query, or a NOT EXISTS correlated sub-query, but I'm not sure that
> would be a performance enhancement - experimentation would be needed
> to see what the costs are.
[SNIP]
Anthony Presley wrote:
> Jonathan Leffler <jleffler@earthlink.net> wrote in message
> news:<bUttb.1339$sb4.655@newsread2.news.pas.earthlink.net>...
>
> [SNIP]
>
>> >>>userData:
>> >>> id serial primary key,
>> >>> dateCreated date,
>> >>> name varchar(100),
>> >>> mileId foreign key (references mileData(id))
>> >>>
>> >>>mileData:
>> >>> id serial primary key,
>> >>> description varchar(100)
>> >>>
>> >>>flagData:
>> >>> id serial primary key,
>> >>> description varchar(100)
>> >>>
>> >>>user_flags:
>> >>> id serial primary key,
>> >>> user_id foreign key (references userData(id)),
>> >>> flag_id foreign key (references flagData(id))
[SNIP]
>> >>>That being said, I need to locate some certain number of user_id's in
>> >>>the following mannger:
>> >>>
>> >>>SELECT FIRST 200 user_id, dateCreated FROM user_flags, userData WHERE>> >>>user_id = userData.id AND (user_id IN (select user_id FROM user_flags
>> >>>WHERE flag_id = 3) AND user_id NOT IN (select user_id FROM user_flags
>> >>>WHERE flag_id = 1)) AND inv_id NOT IN (select user_id FROM user_flags
>> >>>WHERE flag_id = 5) AND inv_id NOT IN (select user_id FROM user_flags
>> >>>WHERE flag_id = 1) AND mileId = 50 ORDER BY dateCreated DESC;
>> >>
>> >>Which table has inv_id in it? Is that indexed? How does inv_id relate
>> >>to user_id?
>> >
>> > My fault .... cutting pasting parts of a query .... inv_id should read
>> > user_id. IE, the query is (assuming I don't have any typos):
>> >
>> > SELECT FIRST 200 user_id, dateCreated FROM user_flags, userData WHERE>> > user_id = userData.id AND (user_id IN (select user_id FROM user_flags
>> > WHERE flag_id = 3) AND user_id NOT IN (select user_id FROM user_flags
>> > WHERE flag_id = 1)) AND user_id NOT IN (select user_id FROM user_flags
>> > WHERE flag_id = 5) AND user_id NOT IN (select user_id FROM user_flags
>> > WHERE flag_id = 1) AND mileId = 50 ORDER BY dateCreated DESC;
>>
>> SELECT FIRST 200 user_id, dateCreated
>> FROM user_flags, userData
>> WHERE user_id = userData.id
>> AND (user_id IN
>> (SELECT user_id FROM user_flags WHERE flag_id = 3)
>> AND user_id NOT IN
>> (SELECT user_id FROM user_flags WHERE flag_id = 1))
>> AND user_id NOT IN
>> (SELECT user_id FROM user_flags WHERE flag_id = 5)
>> AND user_id NOT IN
>> (SELECT user_id FROM user_flags WHERE flag_id = 1)
>> AND mileId = 50
>> ORDER BY dateCreated DESC;>>
>> It is now semi-comprehensible - the original formatting completely
>> loses the substructure. Except that Mozilla wraps the lines, I'd have
>> flattened the sub-queries so they use one per line.
>>
>> Can the last pair of sub-queries be combined into:
>>
>> AND user_id NOT IN (SELECT user_id FROM user_flags WHERE flag_id = 1
>> OR flag_id = 5)
When I see stuff like this, I always wonder what's going on and what the
business problem is that you're trying to solve. It appears to me that your
database design doesn't really support the thing that you're trying to do
very well.
Regular use of NOT-something is a process I've always managed to avoid.
I can't really say anything more constructive than that I'm afraid. Perhaps
you can post the explain plan, but I don't hold out much hope.
--
Ciao,
The Obnoxious One
"Ogni uomo mi guarda come se fossi una testa di cazzo"
Related threads
- Re: Looking for a risk overview
- Those crazy Germans ....
- Re: Oracle 10G
- Retreving Insert Statements for Logical Logs
- FW: IDS to DB2 conversion