…query optimizer and see how easy it is? ;-) Generative…temporary table to count the movies per production year for each kind_id. It was more efficient in this case to force MySQL to…to the query where you were trying to determine the last episode of each TV show. Couldn’t you have used a SUBQUERY to…
Post: Webinar: MySQL 5.6 Performance Schema
May 13, 2013 By 5 Comments
… MySQL 5.6, go over the configuration and spend most time showing how you can use the wealth of information Performance Schema gathers to… Disk IO Problems with excessive temporary tables and external sorts Excessive internal mutex contention Slow queries due to waits on InnoDB locks and…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
…temporary table. What i decided to do is to see how much overhead do on disk temporary tables cause compared to MEMORY tables. To…to 128M ? mysql> show global status like “…to 256M so it would fit everything needed and there was still enough memory for OS cache to keep temporary table. Results: mysql…
Post: Can MySQL temporary tables be made safe for statement-based replication?
May 26, 2008 By 8 Comments
A while ago I wrote about how to make MySQL replication reliable, part of which is to eliminate temporary tables. The idea is this: if a… some changes, create a temporary table with ENGINE=InnoDB, and the InnoDB transaction ID does not change in SHOW INNODB STATUS. The statements…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
SHOW PROFILES shows how much time MySQL spends in various phases of …the Sakila sample database to demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * FROM nicer_…was spent working with temporary tables. But there’s something still missing: it doesn’t show lost time (…
Post: How to obtain the "LES" (Last Executed Statement) from an Optimized Core Dump?
September 9, 2012 By 9 Comments
…tables_state> = { m_reprepare_observer = 0×0, open_tables = 0x7f3eac0085d0, temporary_tables = 0x7f3e84034a50, LOCK_temporary_tables = {m_mutex = { mutex = [...] Great! The query is showing…mysql_execute_command frame. By simply examining “surrounding” variables we’ve been able to…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables…to check which sessions have created which temporary tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables…Run SHOW …
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…table. It also will help the query you’re mentioning allowing group by to be performed without temporary table or external sort. Q: How…how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table…at the leaf nodes? your slide showed single but the fact that you…
Post: SHOW OPEN TABLES - what is in your table cache
December 14, 2008 By 3 Comments
…temporary tables in the table-cache, showing each of them only once (even if table is opened more than ones) In_use show how…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…TABLES WITH READ LOCK to temporary make MySQL read only. In many cases the period for which server has to…to complete. Here is example how it can look: mysql> show processlist; …
