…Table | In_use | Name_locked | +———-+——-+——–+————-+ | test | a | 3 | 0 | +———-+——-+——–+————-+ 1 row in set (0.00 sec) This command lists all non-temporary tables in the table…
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
… still can make server unavailable plus in many cases you can use result overload to make MySQL to consume so much memory so… inside of stored procedures are implemented as temporary tables, so by opening large amount of cursors which are handled as in-memory temporary tables you can…
Post: PHP Large result sets and summary tables.
July 6, 2007 By 8 Comments
… 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 if… BY in question required temporary table plus it was test system anyway. The workaround for this one is to use SQL_BUFFER_RESULT hint…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘ontime_fact’… In general, a column store performs about 8x-10x better than a row store…results in the need for fewer nodes in order to keep data entirely in memory. This frees disk to use on-disk temporary…
Post: Best kept MySQLDump Secret
March 23, 2012 By 13 Comments
…MySQL‘s Transactions work with DDL, In particular ALTER TABLE. When ALTER TABLE is Performed in many cases it will Create temporary table…RESULTS=@OLD_CHARACTER_SET_RESULTS…
Comment: Handling big result sets
June 28, 2006 By 13 Comments
… applies to the cases then temporary table is not used to store full result set before sending it. For Innodb tables it is practically never… around 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: Air traffic queries in InfiniDB: early alpha
November 2, 2009 By 19 Comments
… with previous results. There are … does not store counter of records,…TABLES, which is big limitation from my point of view. As workaround I tried to create temporary table, but got another error: mysql> create temporary table…in Google Spreadsheet or in summary table at the bottom) Query Q1: mysql…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
…stored code (triggers, stored routines, etc) uses memory to execute; so do temporary tables. The following query creates at least 2 temporary tables…mysql> select get_lock(@a, 1); +—————–+ | get_lock(@a, 1) | +—————–+ | 1 | +—————–+ 1 row in… what happens. The results…
Post: Using MyISAM in production
June 17, 2006 By 36 Comments
…results…in-row fragmentation which means single row may be stored in many pieces. In extreme cases I’ve seen over 10 pieces in…tables (you can throw them away if it server crashes). You can use multiple of cache tables to avoid concurrency issues. Temporary tables used in…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
… to current versions of MySQL. Q: How do the upcoming changes in MySQL 5.6 change the… “Using temporary; Using filesort” in the EXPLAIN report. Hopefully, the size of this interim result set is … table as with a non-partitioned table. But since each partition is physically stored as a separate table,…
