… including Percona Xtrabackup, MyLVMBackup and others use FLUSH TABLES WITH READ LOCK to temporary make MySQL read only. In many cases the period…) As you can see FLUSH TABLES WITH READ LOCK is waiting for that very nasty “full join” select to complete. What is… no MYISAM like table lock priority problem with pending WRITE query blocks any READ queries to execute on the table. mysql> show processlist…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…following query executed on the InnoDB table: SELECT non_key_column FROM …mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ … where; Rowid-ordered scan; Using temporary; Using filesort 1 SIMPLE customer …this works only with joins and specifically with Block Access Join Algorithms. So …
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 53 Comments
…used for temporary table creation from the outer query and plus if you use more then one Temporary Tables views which you join together …large derived tables it will become nightmare. So be very careful implementing MySQL VIEWs in your application, especially ones which require temporary table …
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… In MySQL 5.0 with addition of greedy join the problem on looking at too many table join combinations in joins with very large tables is… at which stage MySQL actually perform the sort or creates temporary table and so how much rows will be stored to temporary table. Sometimes it…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 Comments
… a small benchmark which shows MySQL performance when you use 3 different approaches: ENUM, VARCHAR and tinyint (+joined table) columns. In practice you… sort pass (filesort) is required, which also makes MySQL to store Join result in temporary table to do the sort, all together makes things…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
…join order, so we’ll force it with STRAIGHT_JOIN: explain select STRAIGHT_JOIN …. +——-+———–+———–+———————————+ | table | type | rows | Extra | +——-+———–+———–+———————————+ | fact | ALL | 147367284 | Using temporary…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…: 56 Rows_examined: 1113 LOGGING OF THE REPLICATED STATEMENTS Normally MySQL will not write into slow log any queries executed by… from a table. Full_join means any of the joins didn’t use indexes. If a temporary table is required for query execution Tmp_table will be set. Sometimes that table must…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…. You still have some tables in MyISAM format in “mysql” database. Plus HEAP/MEMORY tables are used for temporary tables which may become MyISAM if… FullText currently works with MyISAM tables these are MyISAM related. join_buffer_size Buffer used for joins without indexes and few other cases…
Post: Derived Tables and Views Performance
August 31, 2006 By 14 Comments
Starting MySQL 4.1, MySQL had support for what is called derived tables, inline views or basically subselects in the from clause. In MySQL… materializing them in the temporary table, furthermore temporary table with no indexes (so you really do not want to join two derived tables for example). One…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… of advanced join methods at this point (the work is on a way) – MySQL can’t do hash join or sort merge join – it… to work with in temporary table etc. Prefer full table scans to index accesses – For large data sets full table scans are often faster…. Avoid joins to large tables Joining of large data sets using nested loops is very expensive. Try to avoid it. Joins to smaller tables is…
