… 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… when it is good idea to use query cache ? Third party application – You can’t change how it works with MySQL to add caching…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 53 Comments
… perform some query on this table using indexed column b in where clause: mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test… it’d take if we’ll try to use two separate queries: mysql> SELECT SQL_NO_CACHE * FROM count_test WHERE b = 666 ORDER… case when two queries used. Filesort: 1.8 seconds for SQL_CALC_FOUND_ROWS. 1.8+0.05 seconds in case when two queries used…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… | 888ba838661aff00bbbce114a2a22423 | +———————————-+ 1 row in set (0.06 sec) mysql> SELECT SQL_NO_CACHE name FROM people WHERE age BETWEEN 18 AND …, delivering us expected performance. You can also use this approach when first key column is not in where …
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 Comments
… like to present a small benchmark which shows MySQL performance when you use 3 different approaches: ENUM, VARCHAR and tinyint (+joined table…: 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…
Post: MySQL caching methods and tips
April 4, 2011 By 4 Comments
…cache for many different pieces of data to satisfy a single request is not very efficient. Use multi_get when possible. Once again, caching… and INSERT .. SELECT. These SQL commands can be used to either replace the …unlike the query cache or Memcached. There is no risk of unexpected invalidation…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
… so you do not have to use the union. So changing query to: mysql> SELECT sql_no_cache name FROM people WHERE age in… | +———————————-+ 2 rows in set (0.00 sec) mysql> explain SELECT sql_no_cache name FROM people WHERE age in(18,19,20) AND… to show people in appropriate age group sorting by time when they were last online. If age is fixed this works…
Comment: Using delayed JOIN to optimize count(*) and LIMIT queries
April 8, 2007 By 22 Comments
When using this to optimize a query that has annoyed me for … 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: Getting real life query speeds with MySQL
January 10, 2007 By 10 Comments
… further runs, especially when it comes to disk IO workloads. The attempt to fix it is often using SELECT SQL_NO_CACHE or disabling QueryCache for the test. This works by blocking query cache but still repeated runs… too). Most typically you however are interested on server performance when it is warmed up. This is the stage your server…
Comment: Full text search for all MySQL Storage Engines
June 28, 2006 By 37 Comments
… lookup table you need to focus to get your sql queries correct. Even when they are correct it will still take some… another to shorten the data needed when the search is performed). By using vectors you can use simple bit arithmetics like if you… mysql 4.1.18 was used in all cases – SQL_NO_CACHE was used in order to avoid hitting the query cache during tests): Reversed index…
Post: Are larger buffers always better ?
June 6, 2006 By 9 Comments
…32M. The problem was memory allocation – sorting was used in corelated subquery which was executed hundreds…MyISAM DEFAULT CHARSET=utf8 select sql_no_cache count(*) from t1 where c in (select sql_big_result count(*) from … allocate the buffer and populate if fully when MySQL will read only 5 rows from…
