Calculating query execution time

Question

I have a a table of questions in my database here are column and data types of questions table

Field           Datatype
QID             BIGINT
UserID          INT(11)
Question        VARCHAR(100)
Description     Text
Date            DateTime
Status          TINYINT

this table is expected to have around 2 Million entries my question is how do i calculate query execution time if i am searching a record based on QID, UserID or Question.

you can calculate it but it will be an "estimate" it but you need to provide more information then this.. like create table statement and queries you perform.. — Raymond Nijland, Nov 30 '13 at 12:24

Rahul Tripathi · Answer 1 · 2013-11-30T12:53:53.213

5

You can use the General Query Log but it has certain disadvantages too so think before running it on some production environment.

You can use it like:

SET profiling = 1;

and then execute your query like

SHOW PROFILES;

EDIT:-

I dont know if that is the best approach to go with but here it goes as it may depend on the CPU and the number of processes running on your system:

declare @start timestamp
declare @stop timestamp

set @start = select NOW();
//Your query
set @stop = select NOW();

Execution time = @stop - @start

edited Nov 30 '13 at 12:53

answered Nov 30 '13 at 12:24

Rahul Tripathi

168,305
31
280
331

looks look he wants to calculate query execution times before executing an Query.. – Raymond Nijland Nov 30 '13 at 12:25
@RaymondNijland:- Then I would go with juergen d as I too think that it is not possible! – Rahul Tripathi Nov 30 '13 at 12:26
@RaymondNijland yes i want to calculate it mathematically. – bug Nov 30 '13 at 12:28
@Rahul Tripathi well ive got some algoritmes what are giving an pretty good estimate to real execution time with an best case an worse case boundery but it isnt complete by an long shot because they only work with SIMPLE query types if you involve JOIN it will be pretty challenging.. – Raymond Nijland Nov 30 '13 at 12:32
@RaymondNijland:- Well I think I also know one way but I dont know if that is the best approach to go with. Declare two variable with a timestamp say `@start` and `@stop`. Put the query between these two and then calculate the difference will provide the exection time of the query! – Rahul Tripathi Nov 30 '13 at 12:35
@RahulTripathi did you test your algorithms? – bug Nov 30 '13 at 12:38
@bug:- Yes I have tested it! – Rahul Tripathi Nov 30 '13 at 12:38
@RaymondNijland again that will find out the time of execution in production phase and i want to calculate as an analysis perspective – bug Nov 30 '13 at 12:39
@RahulTripathi that's awesome how much difference is there between real execution time and your results? – bug Nov 30 '13 at 12:41
@bug:- I have updated my answer with the logic which I was saying. Please check that. Although as I said I dont know if that is the best approach to go with but this has worked for me! – Rahul Tripathi Nov 30 '13 at 12:44
1

@bug you know to this as premature optimalisation? i advise you to learn to MySQL internals instead... so you can use this knowledge to design better tables and querys because you know how the engine wil execute them.. – Raymond Nijland Nov 30 '13 at 12:44
@RaymondNijland i know about database design to some level but where do i find resources on MySQL internals and what points should i target performance related? – bug Nov 30 '13 at 12:48
@RahulTripathi in production phase i would do exactly as you've suggested (up) – bug Nov 30 '13 at 12:50
@bug:- Yes that is how most of the people !! – Rahul Tripathi Nov 30 '13 at 12:52

score 1 · Accepted Answer · edited May 23 '17 at 11:49

1

Afaik you can only find out number of block accesses but calculating query execution time would be impossible you can only estimate it because you never know how many processes will be executing on your actual hardware and that's why you have no way to calculate for how long will processor serve your process only.

To find out number of block accesses follow first answer of this question best explanation i guess

edited May 23 '17 at 11:49

Community

1
1

answered Nov 30 '13 at 12:35

user2009750

3,169
5
35
58

2

+1 btw true and it also depend on CPU type.. some CPU's have better Vector calculation optimisation.. iam pretty sure MySQL doesnt optimize execution based on CPU hardware or Disk hardware (it doenst even have an hardware depend Query optimizer) – Raymond Nijland Nov 30 '13 at 12:41
@RaymondNijland exactly – user2009750 Nov 30 '13 at 12:43

Calculating query execution time

2 Answers2