… first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed inside single-statement transactions (TPS… table_open_cache=5000 query_cache_type=OFF performance_schema=0 warmup To warmup server and load data and indices to the buffer…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
… column instead of simply responding with empty set MySQL truncates the constant, performs the lookup and only when discards results: CREATE TABLE `trunc… problems like this to happen: Use consistent data types Make sure you’re using consistent data types for the same values. Often it is…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…lookup is being perform but as only actual rows which are found and returned up to the top level MySQL…MySQL does not count it as access to two tables. In case of “real” access to the data… | id | select_type | table | type | possible_keys | key…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… lookups are performed on table2. Of course this could mean a lot of random lookups… status counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ join_buffer… Innodb_buffer_pool_reads and Innodb_data_read. We can see that with…to run tests to see what specific types of queries would benefit from Hash…
Post: Cache Performance Comparison
August 9, 2006 By 74 Comments
… type of caching is rather helpful for some of applications, which tend to access same data read from database multiple times. Examining MySQL… was selecting from MySQL table. Query vas very simple – lookup by primary cache, so this is kind of peak performance MySQL can provide. Of…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…of point primary key lookups, a range lookup is performed on the primary key …performing the benchmark on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL… 1.89G 1.53G Innodb_data_reads 120552 123872 100551 103011…a. Bad Plan id select_type table type possible_keys key key_len…
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1
October 15, 2009 By 16 Comments
…do you maintain your performance in the face of increasing data size? Here …people use this type of system as I run into this type of application… I can then compare using MySQL to using MySQL + Memcached, and then to …really boost performance. I know you’re thinking my simple key lookup should really …
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…very typical reporting query which queries data for date range for multiple of … same time ? Performing such query MySQL has to build (and do lookups) for all combinations… 2. row *************************** id: 1 select_type: SIMPLE table: g type: index possible_keys: PRIMARY key: …
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…you have your data fully in memory you could perform over 300.000 of random lookups per second … large data sets. In MySQL single query runs as single thread (with exeption of MySQL Cluster) and MySQL… large data sets full table scans are often faster than range scans and other types of index lookups….
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
…Data_length: 17217646764 Index_length: 11993816064 mysql…performing index lookups into the fact table, which is big. An index lookup… | table | type | rows | Extra | +——-+———–+———–+———————————+ | fact | …
