You have a redundant clause in your query - the distinct is not needed when
you are doing a group by on the same field ('Call Svr Tag ID') - not sure
how the optimiser in MySQL will handle this. In some RDBMS's the duplicate
clause will be optimised down to 1 clause, so you wont cop a performance
hit, but just because you think an optimiser will do something is no reason
not to write the query right the first time - always makes it easier on the
optimiser and write the query with as few possible predicates and clauses as
possible.What indexes do you have on the table? You need an index on the 'Journal Create Date' (PS identifiers with spaces in the name are bad practice!). An index on the Call_Cvc_Tag_ID column may also assist with the group by function - you need to run an explain to see what indexes are needed. Also, not sure how the optimiser and indexing in MySQL handles the between predicate, but you might want to try doing a greater than equal to test and a less than test in the place of the between test - eg WHERE Journal_Create_Date >= '2004-03-13' AND Journal_Create_Date < '2004-03-16' In answer to one of the other follow ups, a count() should not result in a full table scan when there are other predicates in the query - they will be resolved first and then the count() will be done over the temporary result set. Cheers, Ben -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Sunday, 18 April 2004 06:50 To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Slow Query Question - Need help of Gurus. Importance: High 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 Java Mailing List For list archives: http://lists.mysql.com/java To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.659 / Virus Database: 423 - Release Date: 15/04/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.659 / Virus Database: 423 - Release Date: 15/04/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
