List:General Discussion« Previous MessageNext Message »
From:Brandon Shuey Date:July 12 1999 8:07pm
Subject:Calculating Query time
View as plain text  
I know there should be a way of estimating how long a query will take to run.  Is there
some general formulas I can use?  Do I need
to run some kind of benchmark for the server Mysql runs on before calculating?  Is there a
rule of thumb?

Here is an example:  Server=Ultra 10, Solaris 2.x, 128Mbtye RAM, IDE HardDrive

mysql> explain SELECT
    -> Date_Format(Date_Time,'%Y-%m-%d') as "Dates",
    -> SourceAccount,
    -> Source,
    -> Count(*) as "Sponsorship Clichthrus 5/3 thru 5/9"
    -> FROM tblGeneralClickthrus p
    -> WHERE (p.Date_Time >= '1999-07-01' AND p.Date_Time < '1999-07-12')
    -> GROUP BY Dates,SourceAccount,Source
    -> ;
+-------+------+---------------+------+---------+------+--------+------------+
| table | type | possible_keys | key  | key_len | ref  | rows   | Extra      |
+-------+------+---------------+------+---------+------+--------+------------+
| p     | ALL  | Date_Time     | NULL |    NULL | NULL | 119597 | where used |
+-------+------+---------------+------+---------+------+--------+------------+
1 row in set (0.10 sec)

mysql> describe tblGeneralClickthrus;
+-----------------+-----------+------+-----+---------------------+-------+
| Field           | Type      | Null | Key | Default             | Extra |
+-----------------+-----------+------+-----+---------------------+-------+
| Date_Time       | datetime  |      | MUL | 0000-00-00 00:00:00 |       |
| GCthruLogID     | char(20)  |      | PRI |                     |       |
| HostAccount     | char(20)  |      | MUL |                     |       |
| SourceAccount   | char(20)  |      |     |                     |       |
| Category        | char(20)  |      |     |                     |       |
| Source          | char(20)  |      |     |                     |       |
| URL             | char(255) |      |     |                     |       |
| UserID          | char(20)  | YES  |     | NULL                |       |
| UserAgent       | char(50)  |      |     |                     |       |
| RemoteHost      | char(50)  |      |     |                     |       |
| RemoteIP        | char(15)  |      | MUL |                     |       |
| Referer         | char(100) | YES  |     | NULL                |       |
| ReferralAccount | char(20)  |      |     |                     |       |
| Server          | char(15)  |      |     |                     |       |
| Quality         | int(11)   |      |     | 0                   |       |
| Summarized      | int(11)   |      | MUL | 0                   |       |
| RefNum          | int(11)   |      |     | 0                   |       |
+-----------------+-----------+------+-----+---------------------+-------+

I want to calculate how long it will take to run the SQL statement:
SELECT
    -> Date_Format(Date_Time,'%Y-%m-%d') as "Dates",
    -> SourceAccount,
    -> Source,
    -> Count(*) as "Sponsorship Clichthrus 5/3 thru 5/9"
    -> FROM tblGeneralClickthrus p
    -> WHERE (p.Date_Time >= '1999-07-01' AND p.Date_Time < '1999-07-12')
    -> GROUP BY Dates,SourceAccount,Source
    -> ;
as stated above.
Brandon Shuey
WebCentric Inc.
(316) 686-4811
brandon@stripped



Thread
Calculating Query time(Brandon Shuey)13 Jul
  • Re: Calculating Query timeChristian Mack17 Jul
  • Calculating Query timeMichael Widenius30 Jul
    • Re: Calculating Query timeBob Bowker30 Jul
      • Re: Calculating Query timeMartin Ramsch30 Jul