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.
