…in_dumptest | +——————–+ | A | | B | | C | +——————–+ 3 rows in set (0.00 sec) mysql> select count(*) from A; +———-+ | count(*) | +———-+ | 2359296 | +———-+ 1 row in set…
Post: Missing Data - rows used to generate result set
July 20, 2008 By 15 Comments
… even though full table scan is performed only rows with COUNTRY=’USA’ are used in results set which clearly puts query as optimization candidate… subquery in the from clause fully and so “use” all rows in result set while in reality only fraction of them will be needed for end result set…
Post: SELECT LOCK IN SHARE MODE and FOR UPDATE
August 6, 2006 By 51 Comments
…tst lock in share mode; +—+ | i | +—+ | 1 | +—+ 1 row in set (0.00 sec) mysql> select * from tst for update; +—+ | i | +—+ | 1 | +—+ 1 row in set (0… results may chance you need to access how it affects your application and perform changes required. What is missing in Innodb locking.? In…
Post: Getting around optimizer limitations with an IN() list
January 9, 2010 By 9 Comments
…row which meets this condition! The reason for this is simply a missing …In this query 60.79 will be floored to 60 (and erroneously included in our results): SELECT * FROM coordinates WHERE x_floor in…rows: 121 Extra: Using where 1 row in set (0.00 sec) SELECT * FROM coordinates WHERE x_floor in…
Post: Air traffic queries in InfiniDB: early alpha
November 2, 2009 By 19 Comments
… surely couldn’t miss a chance to compare it with previously tested databases in the same environment… so you can easy compare with previous results. There are different sheets for queries, datasize …GROUP BY DayOfWeek ORDER BY c DESC; 7 rows in set (6.79 sec) Query Q2: mysql> SELECT DayOfWeek,…
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1
October 15, 2009 By 16 Comments
…? I decided to walk through setting up a very simple application … record 500,001 …. get record 1, miss, read from disk, expunge record 500…row and replace the stored contents on disk C.) Use the data from that row to lookup up a row in…the result via that key. It then uses data from that random row …
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…, because if you set it to some other value say 0.1 seconds, it will miss all queries shorter than …, I would like to point out that the results shown in this blog post are from the queries that I …longest sum of run time, query ranked #1. The first row in the table above shows the Count of number of …
Post: Using Flexviews - part one, introduction to materialized views
March 23, 2011 By 2 Comments
… a result set is really a virtual table. It has columns and rows, just like a database table, but it is temporary in nature… materialized view is similar to a regular view, in that it represents the result set of a query, but the contents are stored… a cache miss is lower because incremental refresh is faster than recomputing the contents from scratch. The contents of the result set are…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
… cache for match. Protection from Multiple Misses So you have item invalidated from …rows used from query syntax may be hard. Query Cache Storage Engines MySQL Query Cache now stores queries in…QuickLZ. Results sets for most queries is highly compressible and light compression can compress results at …
Post: MySQL Query Cache WhiteSpace and comments
March 20, 2008 By 9 Comments
…(*) | +———-+ | 0 | +———-+ 1 row in set (0.00 sec) mysql> /* comment*/ select count(*) from fact where val like “%c%”; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in set (0.00… row in set (8.82 sec) Looking at these results you could judge as all problems are indeed fixed. You can have whitespace in the… query has space after comment deleted) it causes query cache miss. If we look at process list output we can see…
