Downloads

Blog

Contact Us

Handling big result sets

June 26, 2006

Author

Vadim Tkachenko

Insight for Developers

Share this Post:

Sometime it is needed to handle a lot of rows on client side. Usual way is send query via mysql_query and than handle the result in loop mysql_fetch_array (here I use PHP functions but they are common or similar for all APIs, including C).
Consider table:

CREATE TABLE `longf` (
  `f1` int(11) NOT NULL auto_increment,
  `f2` date default NULL,
  `f3` date default NULL,
  `f4` varchar(14) default NULL,
  `f5` varchar(6) default NULL,
  `f6` date default NULL,
  `f7` smallint(6) default NULL,
  `f8` smallint(6) default NULL,
  `f9` varchar(13) default NULL,
  `f10` varchar(39) default NULL,
  `f11` int(11) default NULL,
  `f12` float default NULL,
  `f13` int(11) default NULL,
  `f14` smallint(6) default NULL,
  `f15` varchar(39) default NULL,
  `f16` date default NULL,
  `f17` smallint(6) default NULL,
  `f18` int(11) NOT NULL,
  `f19` date default NULL,
  `f20` date default NULL,
  `f21` varchar(14) default NULL,
  `f22` varchar(6) default NULL,
  `f23` date default NULL,
  `f24` smallint(6) default NULL,
  `f25` smallint(6) default NULL,
  `f26` varchar(13) default NULL,
  `f27` varchar(39) default NULL,
  `f28` int(11) default NULL,
  `f29` float default NULL,
  `f30` int(11) default NULL,
  `f31` smallint(6) default NULL,
  `f32` varchar(39) default NULL,
  `f33` date default NULL,
  `f34` smallint(6) default NULL,
  `f35` int(11) NOT NULL,
  PRIMARY KEY  (`f1`)
);

select count(*) from longf;
+----------+
| count(*) |
+----------+
|  5242880 | 
+----------+

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

CREATE TABLE `longf` (

`f1` int(11) NOT NULL auto_increment,

`f2` date default NULL,

`f3` date default NULL,

`f4` varchar(14) default NULL,

`f5` varchar(6) default NULL,

`f6` date default NULL,

`f7` smallint(6) default NULL,

`f8` smallint(6) default NULL,

`f9` varchar(13) default NULL,

`f10` varchar(39) default NULL,

`f11` int(11) default NULL,

`f12` float default NULL,

`f13` int(11) default NULL,

`f14` smallint(6) default NULL,

`f15` varchar(39) default NULL,

`f16` date default NULL,

`f17` smallint(6) default NULL,

`f18` int(11) NOT NULL,

`f19` date default NULL,

`f20` date default NULL,

`f21` varchar(14) default NULL,

`f22` varchar(6) default NULL,

`f23` date default NULL,

`f24` smallint(6) default NULL,

`f25` smallint(6) default NULL,

`f26` varchar(13) default NULL,

`f27` varchar(39) default NULL,

`f28` int(11) default NULL,

`f29` float default NULL,

`f30` int(11) default NULL,

`f31` smallint(6) default NULL,

`f32` varchar(39) default NULL,

`f33` date default NULL,

`f34` smallint(6) default NULL,

`f35` int(11) NOT NULL,

PRIMARY KEY (`f1`)

);

select count(*) from longf;

+----------+

| count(*) |

+----------+

| 5242880 |

+----------+

And simple PHP script which retrieves all rows from the table:

&lt; ?php
$link = mysql_connect('localhost', 'root', '')
   or die('Could not connect: ' . mysql_error());
mysql_select_db('test') or die('Could not select database');


$time_start = microtime(true);

// Performing SQL query
$query = 'SELECT * FROM longf';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// retrive result
while ($line = mysql_fetch_array($result, MYSQL_NUM)) {
}

// Free resultset
mysql_free_result($result);

$time_end = microtime(true);
$time = $time_end - $time_start;

echo "execution time: ".$time." s";


mysql_close($link);
?&gt;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

< ?php

$link = mysql_connect('localhost', 'root', '')

or die('Could not connect: ' . mysql_error());

mysql_select_db('test') or die('Could not select database');

$time_start = microtime(true);

// Performing SQL query

$query = 'SELECT * FROM longf';

$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// retrive result

while ($line = mysql_fetch_array($result, MYSQL_NUM)) {

}

// Free resultset

mysql_free_result($result);

$time_end = microtime(true);

$time = $time_end - $time_start;

echo "execution time: ".$time." s";

mysql_close($link);

?>

Inital execution time is : 99.04 s.

That’s a bit long and can be improved. How? Let’s look mysql_query function: http://www.php.net/manual/en/function.mysql-query.php. Unfortunately there is nothing interesting for our current task excluding link to mysql_unbuffered_query and this link says:

mysql_unbuffered_query() sends a SQL query query to MySQL, without fetching and buffering the result rows automatically, as mysql_query() does.

So mysql_query buffers all rows on client side. Let’s try our script with mysql_unbuffered_query instead of mysql_query.
execution time: 54.34 s
Good improvement.

The identical results we can get with mysqi_ family. There is mysqli_query function which gets two parameters and second one is result mode : MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT. (Also there can be used pair mysql_real_query / mysql_use_result or mysql_store_result, which are the same). mysqli_query with MYSQLI_STORE_RESULT (used by default) is equal mysql_query, and with MYSQLI_USE_RESULT – is equal to mysql_unbuffered_query.
Results:
MYSQLI_STORE_RESULT: 97.83s
MYSQLI_USE_RESULT: 52.89 s

Next thing which can help is prepared statements. Why?
Let me quote Peter:

If prepared statements are not used server needs to convert millions of values from int to the
string and client has to do reverse conversion. This takes time. Furthemore string data takes more space which also slow down things a bit.

So script with prepared statements:

&lt; ?php
$mysqli = new mysqli("localhost", "root", "", "test");

/* check connection */
if (mysqli_connect_errno()) {
   printf("Connect failed: %sn", mysqli_connect_error());
   exit();
}

$query = 'SELECT * FROM longf';


$time_start = microtime(true);

if ($stmt = $mysqli-&gt;prepare($query)) {

   /* execute query */
   $stmt-&gt;execute();
   $stmt-&gt;bind_result($n1, $n2,$n3, $n4,$n5, $n6,$n7, $n8,$n9, $n10,$n11, $n12,$n13, $n14,$n15, $n16,$n17, $n18,$n19, $n20,$n21, $n22, $n23, $n24,$n25, $n26,$n27, $n28,$n29, $n30,$n31, $n32, $n33, $n34, $n35);

   /* store result */

  $stmt-&gt;store_result();
   
  while ($stmt-&gt;fetch()) {
   }

   printf("Number of rows: %d.n", $stmt-&gt;num_rows);

   /* free result */
   $stmt-&gt;free_result();

   /* close statement */
   $stmt-&gt;close();
}



$time_end = microtime(true);
$time = $time_end - $time_start;

echo "execution time: ".$time." s";

/* close connection */
$mysqli-&gt;close();
?&gt;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

< ?php

$mysqli = new mysqli("localhost", "root", "", "test");

/* check connection */

if (mysqli_connect_errno()) {

printf("Connect failed: %sn", mysqli_connect_error());

exit();

}

$query = 'SELECT * FROM longf';

$time_start = microtime(true);

if ($stmt = $mysqli->prepare($query)) {

/* execute query */

$stmt->execute();

$stmt->bind_result($n1, $n2,$n3, $n4,$n5, $n6,$n7, $n8,$n9, $n10,$n11, $n12,$n13, $n14,$n15, $n16,$n17, $n18,$n19, $n20,$n21, $n22, $n23, $n24,$n25, $n26,$n27, $n28,$n29, $n30,$n31, $n32, $n33, $n34, $n35);

/* store result */

$stmt->store_result();

while ($stmt->fetch()) {

}

printf("Number of rows: %d.n", $stmt->num_rows);

/* free result */

$stmt->free_result();

/* close statement */

$stmt->close();

}

$time_end = microtime(true);

$time = $time_end - $time_start;

echo "execution time: ".$time." s";

/* close connection */

$mysqli->close();

?>

Please note prepared statements also can use $stmt->store_result(); to buffer the result on client side.
The results:

with $stmt->store_result(): 55.20s
without $stmt->store_result(): 39.77s.

So unbuffered query improves execution time by 1.83 times, and unbuffered query with prepared statement protocol by 2.53 times.
It is often though mysql_store_result is faster than mysql_use_result, as you can see in certain conditions it could be opposite.
Prepared statements are typically considered for performance reasons if statement needs to be executed several time – as you can see there is other cases as well – if you have query which retrieves a lot of data from MySQL prepared statements may also perform better, probably because of binary protocol and so no data conversion to string as with standard protocol