…can set cache life time as 12hours. Can this apply to the MySQL innodb memcache? i.e. will the cache …remain permanent? for doing cache clearing off table do i need to do it on SQL side. Also can …write/read vs 5.6 innodb write/read with no memcached layer? Currently I am considering any possible …
Post: Write contentions on the query cache
September 5, 2012 By 5 Comments
… 160ms). Of course, I wanted to know more about how MySQL executes this query. So I used commands you’re probably… and SHOW STATUS LIKE ‘Handler%’ with the SQL_NO_CACHE hint. Will the output be different without SQL_NO_CACHE? Indeed it was. If the Handler…: why did MySQL need so many accesses to the query cache lock? The answer is in the way the query cache works. Simply…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
… thinking MySQL Query Cache is the same as Oracle Query Cache – meaning cache where execution plans are cached. MySQL Query Cache is not. It does not cache …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…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 57 Comments
… clause: 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… take if we’ll try to use two separate queries: mysql> SELECT SQL_NO_CACHE * FROM count_test WHERE b = 666 ORDER BY c… need too check how long our COUNT query would take: mysql> SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 666; Result is…
Post: Query Profiling with MySQL: Bypassing caches
September 12, 2007 By 6 Comments
… frequently I run into question like this “I’m using SQL_NO_CACHE but my query is still much faster second time I… this question is simple – because SQL_NO_CACHE only bypasses query cache but it has no change on other caches, which are MySQL Caches – Innodb Buffer Pool and… more important SAN caches which can be pretty big. So How can you bypass these caches? For MySQL Caches you can restart MySQL and this…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…1000 | 224 | | SOCKET | /tmp/mysql_sandbox5530.sock | /tmp/mysql_sandbox5610.sock | | INNODB_FILE_PER_TABLE | …cache_type and query_cache_size. The behavior is “no cache” by default still but it is achieved differently now. The query_cache…tries to enable it. sql_mode has NO_ENGINE_SUBSTITUTION value …
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
… | +———————————-+ | ed4481336eb9adca222fd404fa15658e | | 888ba838661aff00bbbce114a2a22423 | +———————————-+ 2 rows in set (0.00 sec) mysql> explain SELECT sql_no_cache name FROM people WHERE age in(18,19,20… – very helpful for MySQL Performance optimization: mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> SELECT sql_no_cache name FROM people WHERE…
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
… not in MySQL 5.1 and earlier. I loaded the World sample database and cooked up this query: select sql_no_cache sum(ci… the subquery and basically rewrote it as a stored function. mysql> delimiter // mysql> create function speaks_english(c char(3)) returns integer….Code = c; > return res; > end// mysql> delimiter ; Now the query can be rewritten as this: mysql> select sql_no_cache sum(ci.Population) from City…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
…. Lets see how query times differ in these cases: mysql> SELECT sql_no_cache name FROM people WHERE age=19 AND zip IN (12345…); +———————————-+ | name | +———————————-+ | 888ba838661aff00bbbce114a2a22423 | +———————————-+ 1 row in set (0.06 sec) mysql> SELECT SQL_NO_CACHE name FROM people WHERE age BETWEEN 18 AND 22 AND…
Post: How expensive is a WHERE clause in MySQL?
October 31, 2008 By 17 Comments
… MySQL. The table ended up at 237MB. Now let’s see how long a table scan with no WHERE clause takes: mysql> select sql_no_cache… trivial WHERE clause: mysql> select count(*) from t where a = current_date; (Pop quiz: do I need SQL_NO_CACHE on this query?) The… optimize the WHERE clause itself, only the way that the MySQL optimizer chooses to apply the clause to indexes. (However, as…
