… key_len: 7 ref: const,const,const rows: 104 Extra: Using where But for SELECT id, title, body FROM messages WHERE…: multi key_len: 6 ref: const,const rows: 108 Extra: Using where; Using filesort Now, what can I do to avoid the filesort?
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
… many cache and pre-creation techniques which you can use. Just remember avoiding accessing database is the best way you can optimize database access. This applies to anything else – if you can avoid dynamic page generation at all and have server cache to… – If there is no proper index and query has to use “filesort” it may become a bit problem. Other thing to mention…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
… other products besides MySQL. The logical principles still hold true: using an index really means leveraging a pre-sorted data structure… it as a separate step. This is when we see “Using temporary; Using filesort” in the EXPLAIN report. Hopefully, the size of this… index with all columns in the predicate, add columns to avoid sorting per query, and add further columns to cover the…
Post: GROUP_CONCAT useful GROUP BY extension
September 4, 2006 By 49 Comments
… non-NULL values from a group. Where it can be useful? For example to get PHP array without looping inside PHP… to MySQL server side. Also it can be handy to use result concatenated string as part of IN statement: Sure, last… to add ORDER BY NULL statement after GROUP_BY to avoid unnecessary sorting with filesort // //
Comment: MySQL: Followup on UNION for query optimization, Query profiling
December 15, 2006 By 14 Comments
… in temporary table and sorted by filesort – going to be quite slow. If you’re using UNION – each of the queries in the UNION will produce only 10 rows, using index for sorting not filesort. If… which you still need to do filesort for. Sorting 50.000 rows vs 50 rows is the difference here, now avoiding filesort completely 
Post: Four ways to optimize paginated displays
September 24, 2008 By 27 Comments
…, 20 If the ORDER BY can’t use an index (commonly the case), it uses a filesort. Suppose there are a million rows that meet any WHERE conditions. That means a million rows are retrieved, stored, filesorted… way to avoid some of the costs. Estimate how many results there are. Again, Google does this and nobody complains. Use EXPLAIN…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… any management required or server does everything itself. Especially when using a CMS where DB structure is prepdefined A: MySQL Server… sorting optimization, for more complicated conditions you will need to use something like Trick “Unionizing Order by” described in presentation. Q… mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table or filesort for group…
Post: Optimizing slow web pages with mk-query-digest
April 7, 2011 By 14 Comments
… wai 0 0 0 0 0 0 0 0 # Boolean: # Filesort 0% yes, 99% no # Full scan 7% yes, 92% no… need to figure out what queries were run. I could use mk-log-player and split all sessions that way, unfortunately…-by and order-by within a group so I would avoid the extra step, but then even better than that would…
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
… filesort and not necessary temporary table as we select from one table. To solve sorting issue on big sets, people can use pre-explain. Then decide either use hint to force ordered access or access based on conditions. To avoid explains local in memory statistics can be used to…
