… servers, and in which we clone new slaves from existing slaves using mylvmbackup or a method that is derived from that. Essentially, we are using FLUSH TABLES WITH READ LOCK, and then run… experiencing this with many versions of MySQL. We are now using ‘service mysql stop’ instead of FLUSH TABLES WITH READ LOCK to get…
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 5.0 support… related to each other but how do they compare in terms of performance ? Derived Tables in MySQL 5.0 seems to have different implementation from…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 53 Comments
… and derived tables MySQL 5.0 will fail and perform very inefficiently in many counts. MySQL has two ways of handling the VIEWS – query merge, in… for large derived tables it will become nightmare. So be very careful implementing MySQL VIEWs in your application, especially ones which require temporary table execution…
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
… you couple of hints: Temporary Tables You can build query (with derived tables) which uses as many temporary tables as you like and you… your MySQL server to run out of file descriptors. The same is true for Partitioned tables in MySQL 5.1 Disk Space For MyISAM tables hosting providers used to use disk quotas for MyISAM tables. You can…
Post: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
…ALTER TABLE or OPTIMIZE TABLE now locks small table for few seconds rather than giant 100GB table …in MySQL you might be better of using several queries than doing complicated ones. Of course you would rather use IN… IN() become corellated even if they are not, and so using IN() list of values derived…
Post: Air traffic queries in InfiniDB: early alpha
November 2, 2009 By 19 Comments
… DERIVED TABLES, which is big limitation from my point of view. As workaround I tried to create temporary table, but got another error: mysql… on DERIVED TABLES, as not supported by InfiniDB. Other queries: (again look on comparison with other engines in Google Spreadsheet or in summary table at…
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 elevated, however there are still cases when it may take too long. Most practically important one is if Derived tables (selects in FROM clause) are used. MySQL will execute these subselects on explain to provide…
Post: Which Linux distribution for a MySQL database server? A specific point of view.
December 8, 2011 By 37 Comments
…derive from RedHat and Debian respectively, but their install bases are large enough to mention them separately. Running MySQL…MySQL. By default it not only starts database, but also updates system tables (if needed), scans all tables… such as Slackware and Gentoo. In the beginning I mentioned that…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
… is derived (but not the ratio itself, which you should ignore). Read on for the details. In this … on a machine that’s dedicated to MyISAM tables. Consider your mixture of storage engines (some InnoDB,… instrumentation in MySQL to guide your decisions. It is also not the be-all and end-all of MySQL …
Post: MySQL Optimizer team comments on TPC-H Results
April 14, 2008 By 1 Comment
… notes about little gains in MySQL 6.0. Do not get this post wrong. I’m not saying MySQL 6.0 SubQuery optimizations… is “Derived Tables” AKA “Subselects in FROM Clause” are not optimized yet even though some optimizations may make it into final MySQL 6.0 release. Summary: Poor MySQL 6.0 performance on TPC-H queries does NOT mean to quantify MySQL Optimizer Team work in…
