…makes the replication between servers consistent. If you execute SELECT * FROM id > 1000 … all index records found by the WHERE clause with an exclusive lock and the…statement, the behavior of these locks can be different. In the following link there is a good source of information: http://dev.mysql…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… is so as of MySQL 5.0 The workaround is to modify statement to become SELECT with appropriate where clause. This however would not… most statement, so if you made an error in select in from clause EXPLAIN might never complete. Solution is to run EXPLAIN for statements in FROM clause separately…
Post: How expensive is a WHERE clause in MySQL?
October 31, 2008 By 16 Comments
… * from t; I repeated the last statement until it got slow enough that…WHERE clause takes: mysql> select sql_no_cache count(*) from t; +———-+ | count(*) | +———-+ | 8388608 | +———-+ 1 row in… for the query. If I add another WHERE clause, mysql> select count(*) from t where a = current…
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: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…Every SQL statements breaks down into a relational algebra equation. In Algebra …reduction discussed above. If you have any limitation in resources in a cluster (cpu…SQL, but right now only MySQL storage nodes are supported. … a JOIN, and a WHERE clause that uses an IN clause: — INPUT SQL: select …
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… however is the following – even if you know number of values in insert statement you do not know if all of them need autoincrement… more performance benefits in MySQL 5.1 It also should be possible to start unlocking rows if they do not match where clause (think for example about running not-indexed UPDATE which has to lock all rows in Innodb…
Post: Air traffic queries in InfiniDB: early alpha
November 2, 2009 By 19 Comments
…MySQL‘s LOAD DATA statement…mysql> show warnings; +——-+——+————————————————————+ | Level | Code | Message | +——-+——+————————————————————+ | Error | 9999 | Subselect in From clause is not supported in…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…where. You may not use sub-queries or any non-deterministic functions like NOW() or RAND(). HAVING clauses, ORDER BY clauses…view, as stored in the data dictionary: mysql> select * from flexviews.mview_table where mview_id= … itself is a SQL statement, similar to a regular view. If you choose to use …
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
… are satisfied in the where clause. If a ‘const’ table contains no rows, and it is not used in an OUTER JOIN, then MySQL can immediately… notice is the WHERE clause. You will see that the SELECT statement used in the view has been modified to include the WHERE clause that I…
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…
