… other products besides MySQL. The logical principles still hold true: using an index really means leveraging a pre-sorted data structure… new version promises some exciting new ways that MySQL can use indexes, such as: Index merge improvements. But I expect that… it as a separate step. This is when we see “Using temporary; Using filesort” in the EXPLAIN report. Hopefully, the size of this interim…
Comment: Join performance of MyISAM and Innodb
April 3, 2010 By 6 Comments
… QUERY: 1 SIMPLE ps fulltext prodname prodname 0 1 Using where; Using temporary; Using filesort 1 SIMPLE p eq_ref PRIMARY,i_products_rating_vis…_products_sortorder_vis PRIMARY 4 shoppingcart_5521.ps.productid 1 Using where 1 SIMPLE pi ref i_product_images_imageprodid i…
Comment: JOIN Performance & Charsets
April 3, 2010 By 2 Comments
… QUERY: 1 SIMPLE ps fulltext prodname prodname 0 1 Using where; Using temporary; Using filesort 1 SIMPLE p eq_ref PRIMARY,i_products_rating_vis…_products_sortorder_vis PRIMARY 4 shoppingcart_5521.ps.productid 1 Using where 1 SIMPLE pi ref i_product_images_imageprodid i…
Comment: Why MySQL could be slow with large tables ?
May 27, 2009 By 141 Comments
… Core CPU, 2GB RAM). EXPLAIN this query shows that it uses JOIN perfectly. id select_type table type possible_keys key… Extra 1 SIMPLE stat range dateline dateline 4 NULL 277483 Using where; Using temporary; Using filesort 1 SIMPLE iplist eq_ref PRIMARY PRIMARY 4 vb38…
Comment: What does Using filesort mean in MySQL?
March 5, 2009 By 27 Comments
… Extra 1 SIMPLE sol ALL (NULL) (NULL) (NULL) (NULL) 3 Using temporary; Using filesort 1 SIMPLE dom ALL (NULL) (NULL) (NULL) (NULL) 36 1… 1 1 SIMPLE cts ALL (NULL) (NULL) (NULL) (NULL) 99 Using where 1 SIMPLE cb eq_ref PRIMARY,cou_id PRIMARY…
Post: GROUP_CONCAT useful GROUP BY extension
September 4, 2006 By 49 Comments
…to MySQL server side. Also it can be handy to use result concatenated string as part of IN statement: Sure, …with one query with joins, but sometimes we need the temporary ids in clients code, for example to execute query …NULL statement after GROUP_BY to avoid unnecessary sorting with filesort // //
Comment: What does Handler_read_rnd mean?
June 16, 2010 By 15 Comments
… | | Handler_read_next | 59 | Whereas adding ORDER BY RAND() adds “using temporary; using filesort” to the EXPLAIN, and gives the following: | Handler_read_first…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…_join means any of the joins didn’t use indexes. If a temporary table is required for query execution Tmp_table will… will state that. Filesort is for indicating that filesort algorithm was used, while Disk_filesort means that sorting was done through temporary files. INNODB USAGE…
Comment: MySQL: Followup on UNION for query optimization, Query profiling
December 15, 2006 By 14 Comments
… 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…
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â…
