Large database and speed issues

New User

joined:Apr 2, 2009
posts: 3
votes: 0

So I built a member management system for the company I work for. Right now we have 32,493 members with 69,269 sales to 89 different vendors (all this data and MUCH more is in the database). Now we have phone numbers, fax numbers, and emailaddresses per contact and each member has multiple contacts. We have different identifier numbers that we can use to match our member id's to the member id's that our vendors give us in reports of sales. We also have addresses, zip codes, cities, states to keep track of where our members are from, and we also keep track of every modification to a member's information with the help of a changes table that is growing really quick. Now the problem with all this is that when I built the system, we put in about 5000 records to use as testing, and once the whole system was built and tested it all worked great and pretty fast. But after we dropped the rest of all the data in, it has slowed down alot. It may take 30 seconds for some of the bigger searches and queries to return. We have kinda just been dealing with it but now I have a problem. I have to run a file that is retroactively generating a report from July 1st 1999 to June 30th 2008. The problem is that report has to run once for every single day (thats 6359 days) Thus I get to run a SQL statement that normally takes about 25 seconds to return by itself, 6359 times. The SQL statement is as followes:

SELECT
members.Member_ID, companies.CompanyName, vendors.VendorName, sales.SalesAmount
FROM members
Inner Join companies ON members.Company_ID=companies.Company_ID
Inner Join sales ON members.Member_ID=sales.Member_ID
Inner Join vendors ON sales.Vendor_ID=vendors.Vendor_ID
WHERE (SELECT MAX(SalesDate) FROM sales WHERE Member_ID=members.Member_ID)='".$date."'
GROUP BY members.Member_ID;

"$date" is my variable for the current day that the loop is on

any have any ideas on what I should look up or research to speed this process up or to speed the database up?

///////////////////////////////////////////

I tested the last Select statement, it took 91 seconds to run through Navicat. I then tested it on a duplicate of our database that is on my personal server (took 13 seconds). The difference you ask? The server my company uses sucks and I have had plenty of experience with them sucking but I can't change that. However, after cutting the statement down, I got it to run on my companies server in 7.5 seconds: the new select statement:

SELECT
members.Member_ID
FROM members
WHERE (SELECT MAX(SalesDate) FROM sales WHERE Member_ID=members.Member_ID)='2009-02-03'
GROUP BY members.Member_ID;