… case you’re using simply IN you will get 50.000 rows stored in temporary table and sorted by filesort – going to be quite slow. If you’re using UNION – each of the…, using index for sorting not filesort. If you have 5 queries in the UNION you will have 50 rows in the temporary table…
Comment: MySQL: Followup on UNION for query optimization, Query profiling
December 15, 2006 By 14 Comments
Post: Finding what Created_tmp_disk_tables with log_slow_filter
September 22, 2008 By 3 Comments
… with a client recently I noticed a large number of temporary tables being created on disk. show global status like ‘Created… “tmp_table_on_disk,filesort_on_disk†and I would get only those which use on-disk temporary storage for intermediate results. SET GLOBAL log_slow_filter:= “tmp_table_on_disk,filesort_on_diskâ…
Post: Updated msl (microslow) patch, installation walk-through!
April 20, 2008 By 17 Comments
… patching file sql-common/my_time.c patching file sql/filesort.cc patching file sql/log.cc patching file sql/log…, full_scan, full_join, tmp_table, tmp_table_on_disk, filesort, filesort_on_disk] It allows you to filter queries logged by… to get only those which use on-disk temporary storage for intermediate results “tmp_table_on_disk,filesort_on_disk†would be…
Comment: MySQL: Followup on UNION for query optimization, Query profiling
December 15, 2006 By 14 Comments
… mistake regarding the filesort – in both cases (when using “IN (18,19,20)” and when using “UNION”) mysql makes a temporary table of about the same size on which it does a filesort. Basically, in the first version the results are filtered by age and then a temporary table is created…
Post: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
… the end. The reason for slowness was of course huge temporary table was required (there were about 5 million of distinct… be possible to allocate more memory to the temporary table or switch to filesort method and get result faster. I however picked… – quite handy. Another trick I want to share which I use a lot when I want to analyze data distribution but…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… resources such as network IO or memory to store temporary table, but let us ignore them for a moment. … example is unlikely to help. One helpful way to use this information is to compare systems with different memory … wai 0 0 0 0 0 0 0 # Boolean: # Filesort 0% yes, 99% no # Full scan 0% yes, 99% …
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
… be 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… conditions. To avoid explains local in memory statistics can be used to calculate conditions selectivity. Anyway, question was about set of…
Comment: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 31, 2007 By 57 Comments
… some cases this feature makes sense – when filesort is done or temporary table is used for count(*) query as well as select query… it is too frequently assumed it always makes sense to use instead of 2 queries, which is how it logically should…
