…in MariaDB 5.5 and not in MySQL 5.6, is that because of a bug in MySQL 5.6 code? As MRR was used in both MySQL…does not actually mean that queries with MRR are performing badly. The interesting thing is that though both MariaDB and MySQL…Using where; Rowid-ordered scan; Using temporary; Using filesort…
Post: What does Using filesort mean in MySQL?
March 5, 2009 By 27 Comments
… Percona, and I asked you “what does Using filesort mean in EXPLAIN,” what would you say? I have asked this question in a bunch of interviews so far… you what “too big” means, or I might ask you the other reason temporary tables go to disk!) The truth is, filesort is… Sergey Petrunia’s article on How MySQL executes ORDER BY. You can also read about it in our book, but if you…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… to attack the right query in the right way. But vanilla MySQL does have its limitations, it … Query cache was used or not, if Filesort was used, whether tmp table was created in memory or on…means it accesses these pages in memory. What it says though is that, if this query would run on a cold MySQL…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
…Using where; Using filesort | +—-+————-+——–+——-+—————+——+———+——+——-+—————————–+ 1 row in set (0.00 sec) We can however use UNION to avoid filesort of full table: mysql…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 Comments
…in memory in all cases (and dictionary table does too). Second – the rows are relatively short in… 1439943 Extra: Using temporary; Using filesort *************************** 2. …means sort can’t be done by index and extra sort pass (filesort) is required, which also makes MySQL to store Join result in…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…Using where; Using index; Using filesort | +—-+————-+———-+——+—————+——+———+——-+——+——————————————+ 1 row in…use FORCE INDEX(a) to force MySQL optimizer using right plan. These results mean…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…’re running Innodb with MySQL we can use Innodb_data_reads , Innodb…0 0 0 0 # Boolean: # Filesort 0% yes, 99% no # Full scan… see what the query in average examines 20 rows, which means about 1 …what kind of hardware provides better balance of CPU vs IO utilization as well as as simple as how much does…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… of MySQL Performance problems. Here is what you need…used. Sometimes it means breaking normalization and duplicating column(s) you’re going to use in ORDER BY in…, col2 DESC MySQL will have to use filesort. Classic …in slow query log as query which does not use indexes, even if it is quite fast: mysql…
Post: Descending indexing and loose index scan
May 9, 2006 By 6 Comments
…does not mean it can’t be scanned in reverse order and it will well be. This is how MySQL…in ascending order you will not be able to optimize this query well – external sort (“filesort…In fact it was even implemented in MySQL 5.0, but only for very narrow case of aggregate queries. In…
Post: Estimating Replication Capacity
July 20, 2010 By 4 Comments
…used with context of applying binary logs (for example point in time recovery from backup) replication capacity of 1 will mean…what…does not work in Oracle MySQL Server. set userstat_running=1 and run following query: mysql> select * from information_schema.user_statistics where user=”#mysql…Filesort #…
