List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:July 17 1999 4:08pm
Subject:Re: Calculating Query time
View as plain text  
Brandon Shuey wrote:
> 
> 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

Hi Brandon

AFAIK there is no "rule of thumb" how long a query takes.

But you can measure it with the mysql commandline.

BTW:
The above query is suboptimal. Use "p.Date BETWEEN '1999-07-01 00:00:00' AND 1999-07-12
23:59:59'" to activate the KEY on Date_Time.

Tschau
Christian

PS: Sorry for the late answer, I was on vacation.

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