… is so as of MySQL 5.0 The workaround is to modify statement to become SELECT with appropriate where clause. This however would not tell you full story, especially now as MySQL has triggers… still cases when it may take too long. Most practically important one is if Derived tables (selects in FROM clause) are used. MySQL will…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 7 Comments
…statement: DELETE FROM TreePaths WHERE descendant IN (SELECT descendant FROM TreePaths WHERE ancestor = ‘D’) AND ancestor NOT IN (SELECT descendant FROM TreePaths WHERE ancestor = ‘D’); But MySQL…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…statements representing the original SQL statements. Each MV name will be taken from the table name specified in each statement. You should try each statement…In cases where this is not useful, simply ignore this column. It is used to prevent wide innodb primary keys on the MV. mysql…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… to statement level MySQL Replication which among other requirements, needs all auto-increment values in …cases. Now In MySQL 5.1+ it is also possible to fix this problem for cases when number of rows in…in MySQL 5.1 It also should be possible to start unlocking rows if they do not match where clause…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…cases where there’s a /var/lib/mysql and an /etc/my.cnf, and the server is installed in /customsoftware/mysql…in the FROM clause, which will actually execute the inner subquery!), and examine the query plan. This is where… you have to pull out the statements and analyze them individually. The goal …
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… SQL statements breaks down into a relational algebra equation. In Algebra…case for performance. Optimal mathematical performance requires operations on reduced sets. Keep in…, but right now only MySQL storage nodes are supported….a JOIN, and a WHERE clause that uses an IN clause: — INPUT SQL: select …
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
…. MySQL does this by adding the WHERE clause in the query with ‘where 0…in the EXPLAIN output, but also notice that the type field is set to ‘system’. A ‘system’ table is a special case…WHERE clause. You will see that the SELECT statement used in the view has been modified to include the WHERE clause…
Post: Derived Tables and Views Performance
August 31, 2006 By 14 Comments
…statement. So if you have done mistake in select in from clause…In any case it is pretty annoying gotcha which I hope MySQL will fix in next MySQL versions – the fact queries in…
Post: Debugging problems with row based replication
May 6, 2010 By 10 Comments
…statement. The OLD value (the row that is missing) is represented in the WHERE clause…MySQL slaves to prevent accidental changes in data from happening! *edit* The MySQL 5.1 default binary logging format changed back to STATEMENT in MySQL…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
…in the future. The main thing to watch for, if you do not have full where clause…statements and may end up in slow query log as query which does not use indexes, even if it is quite fast: mysql…
