… key: gid key_len: 4 ref: const rows: 53 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: tb1… key: gid key_len: 4 ref: const rows: 53 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: tb1…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…JOIN states s ON (s.id = c.state_id) WHERE s.name=’Minnesota’ limit 10000,5 \G *************************** 1. row … NULL key_len: NULL ref: NULL rows: 1439943 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table:…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… make use of the multiple indexes. For instance, “SELECT foo FROM bar WHERE indexed_colA = X OR indexed_colB = Y” might use the… Extra: Using intersect(user_type,status,parent_id); Using where; Using index; Using filesort At first glance, this might not look too bad. MySQL is using three…: user_type key_len: 2 ref: const rows: 32404 Extra: Using where 1 row in set (0.00 sec) Making this change…
Comment: Join performance of MyISAM and Innodb
April 3, 2010 By 6 Comments
…) INNER JOIN product_search ps ON p.productid = ps.productid WHERE p.prodvisible = 1 AND (ps.prodcode = ‘gold’ OR TRUE) AND… 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_product…
Comment: JOIN Performance & Charsets
April 3, 2010 By 2 Comments
…) INNER JOIN product_search ps ON p.productid = ps.productid WHERE p.prodvisible = 1 AND (ps.prodcode = ‘gold’ OR TRUE) AND… 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_product…
Comment: Why MySQL could be slow with large tables ?
May 27, 2009 By 141 Comments
… JOIN iplist AS iplist ON (iplist.id=stat.ip_interval) WHERE stat.dateline>=1243382400 AND dateline<1243466944 GROUP BY code ORDER... 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…) (NULL) (NULL) (NULL) 99 Using where 1 SIMPLE cb eq_ref PRIMARY,cou_id PRIMARY 4 lds.ctf.ctf_cou_id 1 Using where
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
… | 1 | NULL | 37915 | Using where; Using filesort | +—-+————-+——–+——-+—————+——+———+——+——-+—————————–+ 1 row in set (0.00 sec) We can however use UNION to avoid filesort of full table: mysql… | 12741 | Using where | | 3 | UNION | people | ref | age | age | 1 | const | 12631 | Using where | |NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | Using filesort | +—-+————–+————–+——+—————+——+———+——-+——-+—————-+ 4…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
… Disk_tmp_table will state that. Filesort is for indicating that filesort algorithm was used, while Disk_filesort means that sorting was done through… DISTINCT c from sbtest where id between 501895 and 502895 order by c; or if InnoDB was not used: # Time: 071031 20…_join: No Tmp_table: No Disk_tmp_table: No # Filesort: No Disk_filesort: No Merge_passes: 0 # No InnoDB statistics available for…
Post: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
…: const rows: 10 Extra: Using where 1 row in set (0.00 sec) mysql> explain select * from tst where i=5 and date… rows: 10 Extra: Using where; Using filesort 1 row in set (0.00 sec) If you take a closer look to WHERE clause you will… key: i key_len: 5 ref: const rows: 10 Extra: Using where; Using filesort 1 row in set (0.00 sec)
