… MRR range scan was needed, which is as efficient as it can be. This is also evident in the query times, which is…_read_key and Innodb_rows_read to be incremented by TWO. It does not actually mean that queries with MRR are performing badly… improvement and changes needed in the optimizer’s cost estimation algorithm. MariaDB 5.5 expands the concept of MRR to improve the performance…
Post: Estimating Replication Capacity
July 20, 2010 By 4 Comments
…estimate replication capacity, so we can deal with replication load before slave is unable to catch up. Need to replication capacity is not only needed…need in the healthy system ? It depends a lot on many things including how fast do you need to be able to…
Post: Estimating Undo Space needed for LVM Snapshot
June 9, 2008 By 5 Comments
… should be enough. Though you may wish to give you some breathing space. Of course depending on database or workload this may need to be… want query log and general logs anyway. However DO place Innodb Log Files on the same LVM volume as you need them to be… estimation would be not to try to save too much on it. Hard drive space is often cheap these days and so it may be…
Post: How to estimate query completion time in MySQL
April 22, 2008 By 14 Comments
… take for the query to complete, depending on what the query is. One of the simplest is to estimate how many rows the query needs to examine, measure… that one query was doing; activity from other queries would have been mixed in there too. It would be great to be able to choose another…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… at queries actually being run and data selectivity to come up with good set of indexes for such case, it also may need to be adjusted… index is how many rows you need to scan to resolve order by (this can be found in slow query log or by examining Hander… showing us there are estimated 1.6 million of rows to be scanned, while we well know it will be just 5 in this…
Post: Getting MySQL to use full key length
January 31, 2007 By 5 Comments
…in this case estimate becomes closer to the truth and this query runs about…not have many un-needed rows accessed causing their pages to be loaded replacing valuable data from cache). MySQL could look at the stats and adjust them appropriately – the plan for second query…
Post: Cache Performance Comparison
August 9, 2006 By 73 Comments
… which also needs to be factored in estimating improvement for real application. So instead of following his route, especially as Jay is going to publish… way to go. File Cache is good for long term storage If you need something stored long term or something which needs to be cached but does not fit even in distributed memory you can use file cache (ie on shared storage). Query Cache…
Post: Four ways to optimize paginated displays
September 24, 2008 By 26 Comments
… mean you need to. Think about how you use such pages. Do you really go clicking directly to the … for my query.” Generally not — you usually look at the most helpful stuff, which is supposed to be first … then this is a simple way to avoid some of the costs. Estimate how many results there are. …
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 7 Comments
… needs to sort a lot of rows to display only few. If we force index as in second query explain will look scary with estimated… these two example queries is about 100 times so it may be quite serious. To fix this issue MySQL would need to better take into… it well can be skewed) using filesort is better as otherwise very large portion of index may need to be scanned to find 10 matching…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…(A) of the index will be used which can be seriously affect performance. Of course …estimated same in original query) and we get the range clause on “info” table instead nested loops join – exactly what we tried to… case you do not even need to trick around equity propagation to make it work) mysql> …
