From: Michael Widenius
Date: July 29 1999 11:09pm
Subject: Calculating Query time
List-Archive: http://lists.mysql.com/mysql/8807
Message-Id: <14240.56989.312297.849132@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

>>>>> "Brandon" == Brandon Shuey <brandon@stripped> writes:

Brandon> 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
Brandon> to run some kind of benchmark for the server Mysql runs on before calculating?  Is there a rule of thumb?

Brandon> 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
-> ;
Brandon> +-------+------+---------------+------+---------+------+--------+------------+
Brandon> | table | type | possible_keys | key  | key_len | ref  | rows   | Extra      |
Brandon> +-------+------+---------------+------+---------+------+--------+------------+
Brandon> | p     | ALL  | Date_Time     | NULL |    NULL | NULL | 119597 | where used |
Brandon> +-------+------+---------------+------+---------+------+--------+------------+
Brandon> 1 row in set (0.10 sec)

You can get a 'rough' time by doing a multiplication on all values in
the 'rows' column in the explain output.

In the above case MySQL will have to examine 119597 rows while
executing the query.

Regards,
Monty