… apply to the MySQL innodb memcache? i.e. will the cache being delete off innodb table after 12hours or they remain permanent? for doing cache clearing off table do i need to do it on SQL side. Also can you also… innodb write/read vs 5.6 innodb write/read with no memcached layer? Currently I am considering any possible usage of…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
… have cached – such as 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…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 57 Comments
…: 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: 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… 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… there is no way to clean Innodb Buffer Pool without restart. For OS Caches on Linux you can use drop caches control available…
Post: Write contentions on the query cache
September 5, 2012 By 5 Comments
… 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…: [...] | Sending data | 0.003067 | | Waiting for query cache lock | 0.000004 | | Waiting on query cache mutex | 0.000002 | | Sending data | 0.003407…. It means that around 50 accesses to the query cache were needed to cache the entire result set. This is what SHOW…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…: select SQL_NO_CACHE city from cities_varchar ORDER BY state limit 10000, 5; Result time(mean): 0.0854793 3) select SQL_NO_CACHE c.city…: select SQL_NO_CACHE city, state from cities_enum limit 10000, 5; Result time(mean): 0.003125 2) Results for VARCHAR: select SQL_NO_CACHE city, state from cities_varchar limit 10000, 5; Result time(mean): 0.003283 3) select SQL_NO_CACHE c.city, s…
Comment: A micro-benchmark of stored routines in MySQL
August 17, 2009 By 7 Comments
…) |+——————–+ | 237134840 | +——————–+ 1 row in set (0.05 sec) mysql> SELECT sql_no_cache sum(ci.Population) FROM City AS ci WHERE CountryCode IN…) | +——————–+ | 237134840 | +——————–+ 1 row in set (0.06 sec) mysql> SELECT sql_no_cache sum(ci.Population) FROM City AS ci WHERE CountryCode IN…
Post: JOIN Performance & Charsets
October 21, 2008 By 2 Comments
…> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select sql_no_cache count(`test`.`t1`.`char_id`) AS `COUNT(t1.char_id…> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select sql_no_cache count(`test`.`t1`.`char_id`) AS `COUNT(t1.char_id…
Comment: Using delayed JOIN to optimize count(*) and LIMIT queries
April 8, 2007 By 22 Comments
… help much, time went from .12sec to .10 sec with SQL_NO_CACHE. Extending this more, to only return one one column on… in the .01 and .00 time frames. Origional query: SELECT SQL_NO_CACHE E.EntryID, E.Body, E.Subject, E.Private, DATE_FORMAT… ORDER BY Date DESC LIMIT 1235,20 Final query: select sql_no_cache E.EntryID, E.Body, E.Subject, E.Private, DATE_FORMAT…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
… to use the union. So changing query to: mysql> SELECT sql_no_cache name FROM people WHERE age in(18,19,20) AND… | +———————————-+ 2 rows in set (0.00 sec) mysql> explain SELECT sql_no_cache name FROM people WHERE age in(18,19,20) AND…; Query OK, 0 rows affected (0.00 sec) mysql> SELECT sql_no_cache name FROM people WHERE age BETWEEN 18 and 20 AND…
