… 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…
Post: MySQL caching methods and tips
April 4, 2011 By 5 Comments
… cache methods for MySQL. Popular cache methods The MySQL query cache When the query cache is enabled, MySQL…MySQL includes two statements that make this easier: CREATE TABLE .. SELECT and INSERT .. SELECT. These SQL… unlike the query cache or Memcached. There is no risk of unexpected invalidation,…
Post: Optimizing repeated subexpressions in MySQL
January 23, 2009 By 11 Comments
How smart is the MySQL optimizer? If it sees an expression repeated many times, does … a little benchmark. The query looks something like this: select sql_no_cache pow(sum(rental_id), 1), pow(sum(rental_id), 2… is an alternative query that returns the same result: select sql_no_cache pow(@sum := (select sum(rental_id) from sakila.rental), 1…
Post: Profiling MySQL Memory Usage With Valgrind Massif
January 9, 2013 By 3 Comments
…compiled ‘out of the box’ (i.e. with no changes), you will likely see less issues …(st_io_cache*, st_mysql_mutex*, Format_description_log_event const*) (in /usr/local/percona/mysql-5….mysql-5.5.28/usr/sbin/mysqld) | | | ->10.10% (7,135,744B) 0x5316EC: handle_slave_sql (in /usr/local/percona/mysql…
Post: Getting real life query speeds with MySQL
January 10, 2007 By 10 Comments
… it is often using SELECT SQL_NO_CACHE or disabling QueryCache for the test. This works by blocking query cache but still repeated runs… kind of other caches and QueryCache is only first in line. There is also all kinds of page caches inside MySQL (key_buffer and innodb_buffer_pool_size) which can be cleared by MySQL restart. Even if you restart MySQL server…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
… starting from record #10000 making MySQL to discard first 10000 records. 1) Results for ENUM: select SQL_NO_CACHE city from cities_enum WHERE…. Indeed there is no cost of the join in this case because there is really no join: mysql> EXPLAIN select SQL_NO_CACHE c.city from… performance, while join performance degraded dramatically. Here is why: mysql> explain select SQL_NO_CACHE c.city from cities_join c JOIN states s…
Post: How does MySQL Replication really work?
January 9, 2013 By 9 Comments
… formats. These are covered pretty well in the MySQL manual here. Put simply, the events can be… written hours or days ago – master may no longer have these cached, so disk reads will occur. If… master status\G” from the master. 2. SQL thread The second process – SQL thread – reads events from a relay…
Comment: A micro-benchmark of stored routines in MySQL
August 17, 2009 By 7 Comments
….Population) |+——————–+ | 237134840 | +——————–+ 1 row in set (0.05 sec) mysql> SELECT sql_no_cache sum(ci.Population) FROM City AS ci WHERE CountryCode IN….Population) | +——————–+ | 237134840 | +——————–+ 1 row in set (0.06 sec) mysql> SELECT sql_no_cache sum(ci.Population) FROM City AS ci WHERE CountryCode IN…
Post: State of MySQL Market and will Replication live ?
April 9, 2008 By 29 Comments
…were a lot of custom technologies created for caching or scalability issues MySQL could not solve well. There are… – some companies I talk to are committed to “no vendor lock in” policy and would not like… of data store and processing was using non SQL and not systems – Google Big Table, Amazon Dynamo…
Post: mk-query-digest, query comments and the query cache
…this information in a specially formatted SQL comment. This information can then be cross-referenced between MySQL logs and Apache logs. …runtime: mysql> set global query_cache_strip_comments=’ON’; Query OK, 0 rows affected (0.00 sec) QueryHitsMissesHitsMisses StrippingNo Stripping no comment1000 …