…updatable-views-with-limit YES userstat FALSE verbose TRUE wait-timeout 28800 Which is however rather ugly and…MySQL Allows you to SET variable to default value (compile time default, not the one … extend SELECT syntax to ease querying of variable global value Right now I can select: mysql> select @@…
Post: What to do with MySQL Full Text Search while migrating to Innodb ?
September 10, 2009 By 12 Comments
…the fact you can’t well mix SELECTs and UPDATEs but also the fact only one update can be happening at the … as MyISAM on one or several of slaves and use it for full text search queries. This approach is … search. In certain cases you can just use MySQL triggers to maintain such table, in other cases …
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
…Updates are rather rare so per-table granularity is not the problem, I have only one server and number of queries…query_cache efficiency. First looking at number of your selects – Com_select and see how many of them are cached. Query…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… covered well, and since there are still a large number of MySQL 5.0 and 5.1 …. Now everyday one of the tables was renamed and swapped and that table was the one that is … start transaction; Query OK, 0 rows affected (0.00 sec) session1 > select * from t1 where i=1 for update; +—+ | i | +—+ | 1 …
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…update last_modified=NOW(); Query OK, 2 rows affected (0.00 sec) (root@localhost) [test]> select * from update…queries per second all trying to lock and update the same row is not going to end well; deadlocks, rollbacks, contention, and…
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
… slightly different query: SELECT MIN(t.update_time) FROM (SELECT uid, MIN(update_time) AS “update_time” FROM foo WHERE update_time IS NOT NULL AND update_time ’0000…-11-06 00:00:00 | +———————+ Very bizarre. On one hand, it seems like MySQL is using date windowing for two-digit dates…
Post: MySQL caching methods and tips
April 4, 2011 By 5 Comments
… generation is a possible solution to the problem. This forces one query to do the computation while others wait for the result… updated with only the information that changed since they were last populated. MySQL includes two statements that make this easier: CREATE TABLE .. SELECT and…
Post: Differences between READ-COMMITTED and REPEATABLE-READ transaction isolation levels
August 28, 2012 By 3 Comments
…entries 1 MySQL thread id 4, OS thread handle 0x7f8dfc35d700, query id 47 … 218786 row locks and only one undo entry. This means that only one row was…SELECT … FOR UPDATE. Useful links Percona Training – InnoDB internals day covers MVCC and locking in detail The SET TRANSACTION ISOLATION LEVEL MySQL…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
…by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(… Notice that there is only one query. If your database system can…UPDATE `word`=VALUES(`word`), `md5(word)`=VALUES(`md5(word)`), `count(*)`=`count(*)` + VALUES(`count(*)`) Now, consider the query with BETWEEN 1 and…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…and what airline offered those flights and the flight number of the flight. Some data hand updated…mysql> use ontime1; Database changed mysql…select count(*) from ontime_fact;” | ./run_query…
