MYSQL "Sending Data" shows a different value each time

Question

I am a student and am trying to test the performance of various databases for a project. What I was trying to do was create 0-99999 (by combining the number into a number table 0-9 several times) and measure the time. My results amaze me, and I wonder if anyone can help explain them (they are shown below).

Here is my test procedure:

BEGIN DECLARE vduration DECIMAL(8,4) DEFAULT 0; DECLARE vid INT DEFAULT 0; DECLARE vcount INT DEFAULT 0; DECLARE vprofilingid INT DEFAULT 0; DECLARE a INT DEFAULT 0 ; simple_loop: LOOP SET a = a+1; FLUSH QUERY CACHE; SET profiling=1; SELECT n1.n + n2.n*10 + n3.n*100 + n4.n*1000 + n5.n*10000 FROM baseline.num n1 , baseline.num n2 , baseline.num n3 , baseline.num n4 , baseline.num n5 LIMIT 100000; SET profiling=0; SELECT COUNT(*) INTO vcount FROM baseline.result; IF vcount=0 THEN SET vid=1; ELSE SELECT MAX(Rid)+1 INTO vid FROM baseline.result; END IF; SELECT MAX(DISTINCT(query_id)) INTO vprofilingid FROM information_schema.profiling; SELECT SUM(duration) INTO vduration FROM information_schema.profiling WHERE query_id=vprofilingid; INSERT INTO baseline.result VALUES (vid, vduration); IF a=5 THEN LEAVE simple_loop; END IF; END LOOP simple_loop; END 

Results:

 RID DURATION 8 0.0406 9 1.8610 10 1.8401 11 1.8558 12 1.8638 

Running SHOW PROFILES shows:

 8 0.04059275 select n1.n+n2.n*10+n3.n*100+n4.n*1000+n5.n*10000 from baseline.num n1, baseline.num n2,baseline.num n3,baseline.num n4,baseline.num n5 Limit 100000 9 1.86098975 select n1.n+n2.n*10+n3.n*100+n4.n*1000+n5.n*10000 from baseline.num n1, baseline.num n2,baseline.num n3,baseline.num n4,baseline.num n5 Limit 100000 10 1.84006350 select n1.n+n2.n*10+n3.n*100+n4.n*1000+n5.n*10000 from baseline.num n1, baseline.num n2,baseline.num n3,baseline.num n4,baseline.num n5 Limit 100000 11 1.85582025 select n1.n+n2.n*10+n3.n*100+n4.n*1000+n5.n*10000 from baseline.num n1, baseline.num n2,baseline.num n3,baseline.num n4,baseline.num n5 Limit 100000 12 1.86381750 select n1.n+n2.n*10+n3.n*100+n4.n*1000+n5.n*10000 from baseline.num n1, baseline.num n2,baseline.num n3,baseline.num n4,baseline.num n5 Limit 100000 

Why is the first time faster? This was against my assumption.

I noticed that there is a difference in SENDINT DATA ,

 SELECT * FROM information_schema.profiling WHERE query_id > 7 and state = 'Sending data'; 

Shows:

 8 10 Sending data 0.040310 9 10 Sending data 1.860891 10 10 Sending data 1.839958 11 10 Sending data 1.855719 12 10 Sending data 1.863717 

In addition, if I set the cycle to run only once, all the results will be about 0.04 seconds. Did I do something wrong? I'm really confused.

Answer 1

Here the link to the entire thread indicates what MySQL has.

http://dev.mysql.com/doc/refman/5.5/en/general-thread-states.html

Pay attention to the sending data section:

Sending data

A stream is reading and processing rows for a SELECT statement and sending data to the client. Since operations that occur during this state tend to perform large amounts of disk access (read), this is often the longest running state during the lifetime of a given request.

Since sending data includes disk read time, the values ​​can vary greatly depending on whether the data was in the hard disk cache, RAM cache, MySQL cache, etc.

Your first request does not read data from disk, so the reading data + sending it step is much faster.