…: 296338 Extra: Using where; Using filesort 1 row in set (0.00 sec) mysql> explain select * from goods force index(cat_id) where cat_id=5… Extra: Using where 1 row in set (0.00 sec) As you can see if given no hint MySQL will prefer to use index… for given seller_id (as it well can be skewed) using filesort is better as otherwise very large portion of index may…
Post: MySQL Optimizer and Innodb Primary Key
October 3, 2006 By 9 Comments
… rows: 1 Extra: Using where; Using index 1 row in set (0.00 sec) mysql> explain select id from myisam where a=3 \G *************************** 1…: 5 ref: const rows: 1 Extra: Using where; Using index; Using filesort 1 row in set (0.00 sec) Filesort should be avoided in this case…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… MySQL are listed below. Configuration Let’s first take a look at the configuration used with different MySQL flavors. MySQL…Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | lineitem | ref | i_l_suppkey | i_l_suppkey | 5 | dbt3.supplier.s_suppkey | 292 | 100.00 | Using where…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=4M… i_o_orderdate 4 NULL 232722 100.00 Using where; Rowid-ordered scan; Using temporary; Using filesort 1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3.orders.o_custkey 1 100.00 Using where 1…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… server does everything itself. Especially when using a CMS where DB structure is prepdefined A: MySQL Server will not automatically define any indexes…: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table or filesort for group by by this column. This works because by scanning data in index order MySQL…
Post: Sphinx: Going Beyond full text search
July 23, 2007 By 18 Comments
… “dev.mysql.com/download”" will only match dev.mysql.com domain and files within /download/ directory. Initially we implemented it in MySQL using partitioning…=const where clause but had to use link like “prefix%” which means index could not be used to get 20 last links and filesort…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…provide our estimates. If we’re running Innodb with MySQL we can use Innodb_data_reads , Innodb_data_writes, Innodb_os_… wai 0 0 0 0 0 0 0 # Boolean: # Filesort 0% yes, 99% no # Full scan 0% yes, 99% …. The queries which are in 100us range are ones where no IO needed to happen so such histogram also …
Comment: MySQL Performance - eliminating ORDER BY function
November 1, 2007 By 6 Comments
… field” like this: EXPLAIN SELECT id, name FROM table_name WHERE id IN ( 222839, 299872, 301535 ) ORDER BY FIELD( id, 222839…_len 4 ref NULL rows 3 Extra Using where; Using filesort Am I plain stupid or is it a bug (in the optimizer) in 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> explain (select * from people where… | 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: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
…: 37748736 Extra: Using where; Using temporary; Using filesort 1 row in set (0.01 sec) mysql> explain select grp, count(*) cnt from dt where slack like “a…: 37748736 Extra: Using where; Using filesort 1 row in set (0.00 sec) As I forced FileSort execution method for GROUP BY by using SQL_BIG…
