… ? Because by default mysql_query uses mysql_store_result C library call and buffers all result set in the process memory. Not good…. This call users underlying mysql_use_result API call which does not store all result set in memory but instead streams it… system anyway. The workaround for this one is to use SQL_BUFFER_RESULT hint if you need to release table locks early. It…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…_nullblogpercona.commanual sql_big_selectsblogpercona.commanual sql_big_tablesblogpercona.commanual sql_buffer_resultblogpercona.commanual sql_log_binblogpercona.commanual sql_log_offblogpercona.commanual sql_log_updateblogpercona.commanual sql_low_priority_updatesblogpercona.commanual sql_max…
Post: Are larger buffers always better ?
June 6, 2006 By 9 Comments
… memory as well as cold provide quite unexpected improvements. sort_buffer_size – recently I worked with case which was running much…=MyISAM DEFAULT CHARSET=utf8 select sql_no_cache count(*) from t1 where c in (select sql_big_result count(*) from t1 where d… some 10 distinct c values. read_buffer_size and read_rnd_buffer_size – These are buffers used by MyISAM to perform reads, in…
Comment: Handling big result sets
June 28, 2006 By 13 Comments
Vadim, This problem of mysql_use_result mainly applies to MyISAM tables as table locks will be … cases then temporary table is not used to store full result set before sending it. For Innodb tables it is practically… by using SQL_BUFFER_RESULT option… It does buffering in temporary table. It is likely to kill all speed benefit on mysql_use_result however 
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…. It is used by SQL layer so applies to all storage engines. key_buffer_size Key Buffer used to buffer Index blocks (row data… (read_rnd_buffer_size). Other storage engines such as Innodb to not use this variable. sort_buffer_size Buffer used to sort result set (allocated by each thread once sorting needs to be done) SQL Layer so works…
Post: Handling big result sets
June 26, 2006 By 13 Comments
… a SQL query query to MySQL, without fetching and buffering the result rows automatically, as mysql_query() does. So mysql_query buffers all rows… 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…
Post: New SpecJAppServer results at MySQL and Sun.
November 12, 2008 By Leave a Comment
… new SpecJAppServer Results More information from Tom Daly can be found here These results are quite … /etc/my.cnf (included in FDA) [mysqld] sql-mode = IGNORE_SPACE transaction-isolation = READ-COMMITTED …=100 table_cache = 6000 read_rnd_buffer_size = 2M sort_buffer_size = 32k thread_cache = 16 …
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
….68 sec) Use of extra temporary table for buffering helps us to get result set we’re looking for and workaround this… completing in 30 seconds rather than 40. Though fetching all result set in this case is still significantly faster. So GROUP… I forced FileSort execution method for GROUP BY by using SQL_BIG_RESULT hint I can see GROUP BY executing about same…
Post: MySQL Session variables and Hints
December 28, 2006 By 2 Comments
… execution of particular query. First is MySQL Hints, such as SQL_BIG_RESULT, STRAIGHT_JOIN, FORCE INDEX etc. You place these directly… sort you can do SET sort_buffer_size=50000000 before executing query and SET sort_buffer_size=DEFAULT after executing the query… is executed, so I could do something like: SELECT SQL_SORT_BUFFER_SIZE=50000000 NAME FROM LARGE_TABLE ORDER BY NAME DESC…
Comment: Why MySQL could be slow with large tables ?
June 19, 2006 By 141 Comments
… resulting rows is large you might get pretty poor speed because temporary table is used and it grows large. Using SQL_BIG_RESULT… messages they will be cached in OS cache or MySQL buffers speeding up further work dramatically.
