Hi All, I have a huge Database which contains about 500,000 records, (will be adding about 20-25k records every week) I need to run group queries and output the same to a web interface.
An example is: SELECT DISTINCT(`Call Svc Tag ID`),Count(`Call Svc Tag ID`) as counter, `Journal Create Date` FROM 31909_859552 WHERE `Journal Create Date` between '2004-03-13' AND '2004-03-16' and `Call Svc Tag ID`<>'null' GROUP BY `Call Svc Tag ID` having count(`Call Svc Tag ID`)>3 order by counter; --basically finding out repeat journal entries over a period of 3 days for the same tag.. (the no. of days may vary.. 2/3/4/5... , and same goes with the having count clause..) The output results to about 150-10000 rows or so.. Based on selections -I've designed the table to be a MyISAM (I don't care how long inserts take, that will be a once-a-week-one-time process on the server, I want the selects to be fast) - At present a select takes anywhere from 20 seconds to 10 minutes on such queries. What can I do? Till now we were using MS Access, and no web based interface. Running a normal MySQL 4.0 install on windows 2000, 256 MB RAM, 2.6 GHz P4 NON-HT About 10 users at a time would be connecting initially. I know theres a lot of tweaking possible with MySQL. Please advise, because I would need a lot of such queries, fetching through JSP, and displaying them in a presentable form. Table Structure is as Follows: Journal Created By varchar(255) No Case Substatus ID varchar(255) Yes NULL Case Status ID varchar(255) Yes NULL Call Rep Employee Name varchar(255) Yes NULL Call Rep Employee ID varchar(10) Yes NULL Call Rep DPS Login ID varchar(10) Yes NULL Call Rep Profile ID varchar(15) Yes NULL Call Rep Section Code varchar(15) Yes NULL Call Rep Country Code char(3) Yes NULL Journal Create Date date No 0000-00-00 Journal Create Time time Yes NULL Call Svc Tag ID varchar(255) Yes NULL Order Num of Svc Tag varchar(255) Yes NULL Indexes: Keyname Type Cardinality Action Field Journal Created By INDEX None Journal Created By Call Rep Employee ID INDEX None Call Rep Employee ID Call Rep DPS Login ID INDEX None Call Rep DPS Login ID Call Rep Profile ID INDEX None Call Rep Profile ID Call Rep Country Code INDEX None Call Rep Country Code Journal Create Time INDEX None Journal Create Time Journal Create Date INDEX None Journal Create Date Call Svc Tag ID INDEX None Call Svc Tag ID Regards, Amit Wadhwa, Data Analyst -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
