… 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: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…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: 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 14 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 30 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 10 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: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…by the tables that I have used in the benchmarks: Table ‘lineitem’: 3.8G …nationkey | 5 | const | 808 | 100.00 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | lineitem | ref | i…different join algorithms. So what does that mean to MariaDB/MySQL users right now with the…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 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: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
…ref: NULL rows: 300363 Extra: Using filesort Surprising? The optimizer preferred…What do we get if we force the optimizer to use the index? mysql…does not fit in memory With the full table scan, the query runs in about 4s. With the full index scan, it runs in… the query, which means that reading the …
