…who asked the great questions. I answered as many as we had time for during the session…try writing a SQL query optimizer and see how easy it is? ;-) Generative grammars like SQL …more frequently. Nearly every solution for randomly choosing rows involves some compromise, either of performance, or …
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… around as long as they don’t touch the same row. Row locks are held for modifications until the transaction commits and… my application? Think about your workload. How frequently do you update any given row? We call rows that are updated heavily “hotspots”. Examples…: UPDATE achievements SET count = count + 1 where achievement = ‘killed_troll’; How many players might accomplish this achievement at the same time? Example…
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
… There are numerous solutions for MySQL high availability. Many rely on MySQL’s asynchronous replication to …tail -f slow.log`, except that it knows how to follow the log stream across log rotation… step because update statements that match no rows or insert statements that have duplicate key …
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… are many possible ways query can be optimized. There is however one extremely helpful metric which you can use – ratio between rows… to understand how much optimization is possible. For example I might have query which uses some index scans 1000 rows and sends… optimizing ? – see how many rows query sends after group by, distinct and aggregate functions are removed (A) – look at number of rows examined divided…
Post: How to estimate query completion time in MySQL
April 22, 2008 By 16 Comments
… run a query in MySQL and wondered how long it’ll take to complete? Many people have had this experience. It’s…. One of the simplest is to estimate how many rows the query needs to examine, measure how fast it’s working, and do the… do that.) The solution was to measure how fast the query was scanning rows in the table scan of the fact table…
Post: How fast can MySQL Process Data
April 9, 2008 By 3 Comments
… memory table with very narrow row and run simple query which needs to do simple filtering – how many rows per second it will be… we add some math the row scan speed is significantly affected. I also decided to see how longer rows affect performance and created… status like “m4″ \G *************************** 1. row *************************** Name: m4 Engine: MEMORY Version: 10 Row_format: Fixed Rows: 5000000 Avg_row_length: 129 Data_length: 685609952…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… approach to take number of rows estimated from each row in a join and multiply them to see how many rows will be examined in total. This can be rather wrong. One thing to consider is number of rows analyzed… row combinations really examined. LIMIT is not taken into account while estimating number of rows Even if you have LIMIT which restricts how many rows…
Post: Just do the math!
July 6, 2009 By 4 Comments
… you’re doing some reports from your apache log files – how many distinct visitors hit the page and stuff like that. You… typical one – scan the table, when for each row insert (or update) row in the temporary heap table. After temporary table is… you can also see how many rows query can process per second in total. Lets assume it is 100K rows per second when data…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… what if you have application which can perform search on many different columns, with worse then perfect selectivity. Various social networking… do not have full where clause resolved by index is how many rows you need to scan to resolve order by (this can….6 million of rows to be scanned, while we well know it will be just 5 in this case. Many of the…
Comment: Innodb Caching (part 2)
May 10, 2011 By 7 Comments
… tracks how many distinct pages were accessed in addition to how many rows were accessed. If you have close number of pages compared to rows you’re only touching few rows per page… even though individual queries touch only one row per page your working set is many rows per page, for example the “recent” pages…
