Re: Slow Query Question - Need help of Gurus.

Michael Stassen Sun, 18 Apr 2004 23:01:36 -0700

Ben Dinnerville wrote:

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.

DISTINCT is not a function you feed a column into. It is a SELECT keyword which prevents duplicate rows. For example,

  CREATE TABLE t (v1 int, v2 int);
  INSERT INTO t VALUES (1, 1), (1, 2);
  SELECT DISTINCT(v1), v2 FROM t;
  +------+------+
  | v1   | v2   |
  +------+------+
  |    1 |    1 |
  |    1 |    2 |
  +------+------+
  2 rows in set (0.00 sec)

Once you add GROUP BY, you are guaranteed unique rows, one for each group, so DISTINCT adds nothing.

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_Svc_Tag_ID column may also assist with the group by
function - you need to run an explain to see what indexes are needed.

The indexes were listed at the bottom of the original post. He already has indexes on `Journal Create Date` and `Call Svc Tag ID`, but MySQL will only use one index per table, so separate indexes won't help much. He should definitely run an EXPLAIN. I expect EXPLAIN will list both as possible keys, and which, if any, it picked. What is needed, I expect, is a multi-column index on those 2 columns:

  ALTER TABLE 31909_859552
  ADD INDEX Tag_Created (`Call Svc Tag ID`, `Journal Create Date`);

Then try again:

  SELECT `Call Svc Tag ID`,
          Count(*) 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 counter > 3
  ORDER BY counter;

Note that sorting by the count can't use an index, so it will be slower than if you had ordered by `Call Svc Tag ID`.

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'

MySQL handles BETWEEN just fine. If you think about it, you explicitly set the range with BETWEEN, but the optimizer has to put the two inequalities joined with AND together to get the same range. In other words, BETWEEN is easier.

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]

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.

<snip>

- At present a select takes anywhere from 20 seconds to 10 minutes on
such queries.

MySQL/MyISAM is designed to give fast SELECTS, so 10 minutes seems excessive.

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'm no Win2000 expert, but that doesn't sound like enough RAM, especially if the web server is on the same machine. Ideally, you'd like your data cached in RAM, but this one table appears to be 100 Mb or so, not even counting the indexes. With a query taking 10 minutes, I wonder if you're running out of memory and thrashing the disk.

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

Michael


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Re: Slow Query Question - Need help of Gurus.

Reply via email to