… using mylvmbackup or a method that is derived from that. Essentially, we are using FLUSH TABLES WITH READ LOCK, and then run…). We are using CentOS 5.x, LVM2, XFS and Oracle MySQL 5.5.x (x=16 on many boxes atm), but… 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… | +—+———————————-+ 1 row in set (0.03 sec) Same query using derived table crawls: mysql> select * from (select * from test) t where i=5…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 53 Comments
… you assume MySQL would optimize your VIEWs same way as more advanced database systems would. Same as with subqueries and derived tables MySQL 5.0 will fail and perform very inefficiently in many counts. MySQL has two ways of handling… 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: SELECT UNION Results INTO OUTFILE
September 14, 2011 By Leave a Comment
…the SELECT INTO OUFILE from this. Using the employees.employees table: mysql [localhost] {msandbox} (employees) > EXPLAIN SELECT * INTO OUTFILE ‘/home/viiin/sandboxes…sec) You will notice the second query without the additional derived
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… 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: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… 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… rows by reading data from other tables. No information about using disk for sort or temporary tables MySQL always calls it “filesort” even…
Post: Which Linux distribution for a MySQL database server? A specific point of view.
December 8, 2011 By 37 Comments
… and Ubuntu derive from RedHat and Debian respectively, but their install bases are large enough to mention them separately. Running MySQL won…) script for MySQL. By default it not only starts database, but also updates system tables (if needed), scans all tables for consistency problems…
Post: MySQL Optimizer team comments on TPC-H Results
April 14, 2008 By 1 Comment
… any other benchmarks, rather they look at the queries which MySQL Users have problem with and first target the most common… 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…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
… on the param1_val value in the params table: mysql> select * from v2; +——+———-+ | c1 | count(*) | +——+———-+ | 10 | 130 | +——+———-+ 1…| NULL | NULL | NULL | 1 | | | 2 | DERIVED | p | const | PRIMARY | PRIMARY | 260 | | 1 | | | 2 | DERIVED | t2 | ref | c1 | c1 | 5 | const…
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… DERIVED TABLES, as not supported by InfiniDB. Other queries: (again look on comparison with other engines in Google Spreadsheet or in summary table…
