… SELECT SQL_CACHE col from foo where id=5. If you run in default mode you can also use SQL_NO_CACHE to block caching for certain queries, which you know do not need to be cached. Counting query cache efficiency There… which queries are cached, as well as overhead query cache provides. One portion of query cache overhead is of course inserts so you can…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 57 Comments
… ($i = 0; $i < 10000000; $i++) { $b = $i % 1000; mysql_query("INSERT INTO count_test SET b=$b, c=ROUND(RAND()*10…: mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 555 ORDER BY c LIMIT 5; Results with SQL_CALC_FOUND… 1 row in set (0.00 sec) mysql> explain SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 666; +—-+————-+————+——+—————+——+———+——-+——+————-+ | id | select_type…
Post: Knowing what pt-online-schema-change will do
November 1, 2012 By 1 Comment
….`score`) Not copying rows because this is a dry run. INSERT LOW_PRIORITY IGNORE INTO `test`.`_t_new` (`id`, `guest_language… `score` <= ?) ) /*pt-online-schema-change 10451 copy nibble*/ SELECT /*!40001 SQL_NO_CACHE */ `guest_country`, `guest_language`, `score` FROM `test`.`t` FORCE INDEX…
Post: How expensive is a WHERE clause in MySQL?
October 31, 2008 By 17 Comments
… NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into t(a) values(current_date); insert into t select * from t; I repeated…’s see how long a table scan with no WHERE clause takes: mysql> select sql_no_cache count(*) from t; +———-+ | count(*) | +———-+ | 8388608 | +———-+ 1 row…
Comment: MySQL Query Cache
September 30, 2009 By 70 Comments
… updated, or inserted into, all queries in the cache which use that table, will be invalidated. Remember the Query Cache caches the result set packets, its not caching query execution plans like SQL Server does. So no, the Query cache will not be… your big volatile table, and add SQL_NO_CACHE to them. This means mysql wont bother wasting time caching the results, only for them…
Comment: MySQL Partitioning - can save you or kill you
September 14, 2012 By 45 Comments
Michael — Are you sure the Query cache did not kick in? Try SELECT SQL_NO_CACHE (etc). Raghu — I’m surprised you don’t have trouble on the Slaves. Remember that every INSERT on the…
Comment: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
July 7, 2008 By 30 Comments
… read something about frequent inserts with indexes being slow. but would indexing speed things up? and also SELECT SQL_NO_CACHE c.city FROM… blog, but since you guys said its dangerous, it’s no longer in my options.
Post: MySQL caching methods and tips
April 4, 2011 By 5 Comments
…INSERT .. SELECT. These SQL commands can be used to either replace the contents of, or insert new data into the summary table. One advantage of summary tables is that they are persistent unlike the query cache or Memcached. There is no risk of unexpected invalidation,…
Post: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
January 18, 2007 By 15 Comments
…and INSERT ON DUPLICATE KEY UPDATE. Really, Why MySQL has both of these, especially both are non ANSI SQL extensions … only had ISAM tables with table locks and no transactions support. Of course you could use LOCK …8K in size and are not aligned to OS cache page OS still would need to perform read …
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…cache_disk_useblogpercona.commanual binlog_stmt_cache_sizeblogpercona.commanual Binlog_stmt_cache_useblogpercona.commanual bootstrapblogpercona.commanual bulk_insert…
