…problems with MySQL 5.5 with addition of user variables, stored procedures, prepared…_length+index_length) | +——————————-+ | 126984 | +——————————-+ 1 row in set (0.98 sec) This … global memory allocation and will result in increased memory allocation until …
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…table. mysql> show processlist; +——-+——+———–+———-+————+——+——————————+——————————+———–+—————+———–+ | Id | User | Host … 5 rows in set (0.00 sec) As result…stored procedures etc (which are stored…
Post: MySQL Stored Procedures problems and use practices
June 12, 2007 By 21 Comments
… stored procedure calls. On the other hand Stored Procedures Indeed can help to Improve MySQL Performance. For DBT2 benchmarks we’ve tried a while back MySQL 5.0 was about 10% slower than 4.1 without stored procedures but was 20% faster if Stored Procedures are used… result set back to the client as in this case server may need to do more work to send it back than Stored Procedure…
Post: PHP Large result sets and summary tables.
July 6, 2007 By 8 Comments
…) like crazy. Why ? Because by default mysql_query uses mysql_store_result C library call and buffers all result set in the process memory. Not good… much more efficient. Another alternative is of course to use MySQL Stored Procedures which can be fit to do this simple job. The…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… described materialized views (MVs). An MV is essentially a cached result set at one point in time. The contents of the MV…, Flexviews includes a MySQL stored procedure API, called the SQL_API which is used to define the view. These stored procedures maintain the Flexviews data…
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
… normally treated very quickly by MySQL. The moment you give someone access to MySQL Server, all MySQL Security guarantees is you … their queries. Stored Procedures – How much memory can stored procedure allocate ? say can you create 1000 variables in stored procedure and set 1M result set to each …
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
… SELECT queries are cached SHOW commands or stored procedure calls are not, even if stored procedure would simply preform select to retrieve data from… of them quite likely would not have change their result set but MySQL has no way to identify which one of them would… cache option query_cache_limit was implemented which limits result set which can be stored in query cache. If you need larger queries…
Post: MySQL Server Memory Usage
May 17, 2006 By 18 Comments
… is why: List of rarely considered MySQL Server Memory Requirements Thread buffers can…step, especially specially crafted ones. Stored Procedures. Compex stored procedures may require a lot of memory…This may have unbound memory requirements retriving result sets from remove queries. Blobs may require…
Post: How fast can MySQL Process Data
April 9, 2008 By 3 Comments
…stored procedure to make timing easier: mysql> DELIMITER // mysql> CREATE PROCEDURE test_read(pl INT) -> BEGIN -> DECLARE t INT; -> SET @x = 0; -> REPEAT SET…
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
…results here, your comments welcome — is to see how fast the SQL procedure…+——————–+ | sum(ci.Population) | +——————–+ | 237134840 | +——————–+ 1 row in set (0.23 sec) This …rewrote it as a stored function. mysql> delimiter // mysql> create function speaks_english…
