I am new to MySQL but I hope that someone in this group can help out. I
am doing a research study on speeding up processing with database
The platform is a Pentium 4 2.66GHz Pc with 512MB of memory. Now I tried
increasing the memory to 2 Gigabytes but it did not seem to improve the
performance in any way. I don't see a lot of swapping to disk and the
database itself is easily able to fit into the 512kbytes of memory.
The operating system is Linux RH9.0 and I am using Mysql 5.0.
Does MySQL have some way of easily handling a sliding window? I could
only
do it by using a series of recursive queries that repeated The basic
query shown below over until the career of each player was Completely
covered. There are about 15000 players and 80,000 rows in the Batting
table itself.
The Query that I wrote takes 11 minutes but an oracle query only takes
about 1 minute
************************************************************************
****************************************************************
use baseball;
##########THIS IS THE M TABLE WHICH GETS THE AVERAGE IN A 5 YR
WINDOW###########
SELECT round(avg(batting.HR),2) as AVG_HR,
batting.HR as HR, batting.yearID as "YR",
#l.nameLast, l.Lname,l.maxyr, l.minyr, l.maxyr- l.minyr
as "YRS_PLAYED",l.PlayerID as "PlyrID"
FROM
#######THIS ADDS THE L QUERY TO FIND ALL PLAYERS WITH >= 5YR
CAREERS#########
(
SELECT * FROM
#######THIS COMBINES THE J AND K QUERIES INTO A SINGLE K QUERY TO FIND
MAX AND MIN YEARS######
(SELECT m.nameFirst as "Fname", m.nameLast "Lname",
max(b.yearID) as "maxyr", min(b.yearID) as "minyr",
b.PlayerID, b.HR , max(b.yearID) -min(b.yearID) as
"YRSPLAYED"
from master as m, batting as b where (m.PlayerID
=b.PlayerID) and
b.HR >0 group by b.PlayerID
)as k WHERE maxyr-minyr > 3 group by k.PlayerID
#limit 10 ) as l,batting
where (batting.PlayerID = l.PlayerID) and (batting.yearID
<= l.maxyr)
and (batting.yearID >= l.maxyr - 4) and (l.maxyr - l.minyr
>= 3)
group by l.Lname limit 10;
00:11:31 THIS IS THE ELAPSED TIME FOR WINDOW QUERY
As can be seen the time is 11 minutes and 31 seconds
Best regards,
Peter Reali
