Unbuffered result sets in MySQL? I must have been last to know…
Peter Zaitsev over at MySQL Performance Blog has a post entitled PHP Large Result Sets and Summary Tables. This post completely caught me by surprise because (embarrassingly enough) I had no idea you could use unbuffered result sets with mysql and mysqli.
This is a wonderful post which will allow me to put some ugly hacks to bed. The commands to use unbuffered result sets are mysql_unbuffered_query() and mysqli_use_result(). In the post Peter mentions some of the pitfalls with using unbuffered queries:
- Table Locks – Tables remain locked until the entire result set is dealt with
- Connection Sharing – with a buffered result set, you can perform other queries on the same connection while you traverse the result set. With an unbuffered query, the connection remains busy.
- Error trapping – Because you are fetching data on the fly, you will need to increase your error trapping.
- Connection timeouts – If you spend too much time processing, and have long periods of time between result set fetches, you can end up timing out.
You also lose some functionality like mysql_num_rows() and mysql_data_seek() that rely on the buffered result set.
Vadim Tkachenko (also of Mysql Performance Blog) gets into more detail in a post from last year entitled Handling Big Result Sets. This post talks about unbuffered queries as well as when you can expect a performance boost from using mysqli prepared statements ( a good reason to use mysqli).
