… uniformly over the range 1..MAX(id)? Yes, there’s a risk if you have irregular distribution of matching rows that you… random number generator picks values in that range with equal frequency as any other range, then the movie immediately following the “gap… row with “=” instead of “>” and be guaranteed to find exactly one match. But the tradeoff of this solution is that it requires…
Comment: Sample datasets for benchmarking and testing
February 4, 2011 By 18 Comments
… distributions and properties of the data that test a broad range of characteristics not possible with real-world data sets. For… a collection of synthetic workload generators with parameter sets that exactly match the distribution, dynamics, and skew of real-world data sets…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… when it stops doing so assuming there are no more matching. The RANGES such as AGE BETWEEN 18 AND 20 work similar… we spoke about how MySQL uses the index but not exactly what it gets from the index – typically (unless it is… OK, 10900996 rows affected (6 min 47.87 sec) Rows matched: 11010048 Changed: 10900996 Warnings: 0 Lets see what happens in…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…). Instead you could use JOIN to get list of days matching range from some pre-generated table and use the join to… see it does not work while I know I used exactly this trick to optimize some nasty queries. It looks like… original query) and we get the range clause on “info” table instead nested loops join – exactly what we tried to avoid. It…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… whole table if you a) choose your aggregation to match the order of your common queries and b) choose … each file can help a lot. Store the ranges of values for various attributes, for example; or the… row, and an ad that gets one impression takes exactly the same: one row. An impression per day …
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
…for successfull MySQL Performance Optimization is understanding when exactly MySQL is going to use index and … to use multiple keyparts if first keyparts matched with “=”. Here is example: mysql> explain SELECT …is not what you would intuitively expect – why range which covers 5 rows is hundreds of …
Post: Differences between READ-COMMITTED and REPEATABLE-READ transaction isolation levels
August 28, 2012 By 3 Comments
…. In READ COMMITTED the locks that did not match the scan are released after the STATEMENT completes. … the same SELECT, then you will see the exact same resultset that you saw at 5AM. This …In REPEATABLE READ InnoDB also creates gap locks for range scans. select * from some_table where id > 100 …
Post: Optimizing slow web pages with mk-query-digest
April 7, 2011 By 14 Comments
… particular pages were taking minutes to load and that’s exactly what the customer asked me to focus on. So instead… report because it matches –limit. # Scores: Apdex = 0.97 [1.0], V/M = 19.02 # Query_time sparkline: | ^______| # Time range: 2011-04… –filter=’$event->{Thread_id} == 160847′ in > out Now I know exactly what needs to be fixed first to make the greatest…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… faster for IO Bound Index scans and large range scans. We surely should benchmark this aspect separately. …possible to start unlocking rows if they do not match where clause (think for example about running not-…in Innodb right now) I however did not get exact plans on this one. In general I should …
Post: Is VoltDB really as scalable as they claim?
February 28, 2011 By 3 Comments
…had to adjust the computed 1-node performance slightly (ranging from 1/2 of a percent to 8 percent… against this kind of data isn’t an exact science anyway — there is some human judgment involved (…from what I know of VoltDB it does indeed match their claim, with good scalability to dozens of servers…
