Re: [GENERAL] Expensive query

At 0:13 +0200 on 31/10/98, Dustin Sallings wrote:

>
> That users table looks kinda useless. I'd recommend changing id
> to name and making id an int, then putting your events users in as ints.
> Currently, it doesn't do anything at all, and you might as well be doing
> this query:
>
> select distinct id from events where code = 'Whatever'
> and age('now',when) <= ' 1 day';
>
> I would also recommend not making code a char(10). If you put an
> index on code and an index on date, you should be able to get your results
> pretty quick. If you change the userid and the code to integers and do a
> three table join, it might be faster, but it would use *much* less disk
> space.

I don't get your solution. Doesn't it return the exact opposite of what is
needed? He needed the users which did NOT have code 'Whatever' and also
didn't have an event the last day - and your query asks which users have a
'Whatever' event less than a day ago...

If what you intended is to put this query into a NOT IN subquery, then it's
still not what was required, because we didn't want to exclude all
'Whatever' events of the last day. We wanted to exclude both 'Whatever'
events and events of the last day.

No, it's either two subqueries or a subquery with an OR, which is not
optimized. Perhaps changing to a NOT EXISTS rather than a NOT IN query
would help:

select id from users
where
NOT EXISTS (
select * from events
where userid = id
and code = 'some code'
)
and
NOT EXISTS (
select * from events
where userid = id
and age('now',when) <= '1 day'
);

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma