… 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> …
Post: How does MySQL Replication really work?
January 9, 2013 By 9 Comments
… as on replication being single-threaded, on semi-synchronous replication or on estimating replication capacity, …master. For replication to work, first of all master needs to be writing replication events to a special log… also want to look at diskstats or even do a query review for SQL thread to get…
Post: How much space does empty Innodb table take ?
December 16, 2008 By 7 Comments
…test_innodb(a int, b int) engine=innodb; Query OK, 0 rows affected (0.30 sec)…needs to be allocated in main tablespace for Innodb data dictionary. But that one is pretty small. This is the good reason to… (estimated number) which means this value is going to be changing back and forth and it would be …
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…’d like to look at basics – what resources are really needed to provide resource for given query ? It surely needs CPU cycles, it may need disk IO. You may also need other resources… Directors would like to know the costs in “utility” model and you do not have to be in the cloud to provide them with estimates.
Post: MySQL Sizing questions
June 23, 2006 By 4 Comments
… barely technical) need estimates for their plans and they would rather take wrong estimates than no estimates. Now than these estimates turn out to be wrong they… need estimates, however we need to spend time doing serious capacity planning, probably look at real data try tunning sample queries etc. This should be serious…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… even more. Computing rows to be examined is more complicated It is frequent approach to take number of rows estimated from each row in… be required on disk. In many cases it is impossible to tell as we do not know how much data we’ll need to… are used in queries. In general in my opinion EXPLAIN needs serious overhaul so it can be used with GUI tool to provide tree…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…BTREE indexes – this index type is very good to be able to quickly lookup the data on any its prefix…to 4.5% it is most likely better to use single index only and simply filter out rows we do not need… decision it could do. Indeed the query takes 360ms Note also the estimated values of “rows” is very …
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… all but will estimate number of rows during query execution. I typically look at ANALYZE TABLE and adding it to the table if… it run helps to get good plans. If query plans are good or bad independently of it being run there is need to bother – for bad plans use FORCE INDEX or change the query and report MySQL…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
…estimation from the optimizer and sometimes the real query can use a different execution plan. pt-index-usage tries to convert non-SELECT queries to…to check and benchmark the changes on our testing environment to measure the impact on performance. You won’t need to…
