I have a select statement that returns a set of 74,000+ results back in
under a minute as follows:
select s.sessid, s.membid, s.datetime
from sessions2 s, (select min(datetime) as datetime, membid from sessions2
where membid is not null group by membid) as minsess
where s.membid = minsess.membid
and s.datetime = minsess.datetime;
The final cost from explain for the above select is 22199.15 ... 24318.40
with rows = 5 and width = 28.
Then I issue an update as follows (to update those 74,000+ rows):
update sessions2 set sinceinception = 0
from sessions2 s, (select min(datetime) as datetime, membid from sessions2
group by membid) as mindate
where s.membid = mindate.membid
and s.datetime = mindate.datetime;
The final cost from explain for the above update is 31112.11...98869.91 with
rows = 2013209 and width=87.
This update statement has been left running over night and does not
complete. The ram usage on the machine is at about 3/4 capacity (800mb)
during this time and CPU usage is near 100%. The machine has the -F option
set and memory segments of 200mb and is running 7.1.2.
What could be causing this update statement to not complete?
Why are the costs so different since it seems to me that besides the cost of
the update they are the same query?
Any help would be great!
Jeff Barrett
