…index query down to one disk seek.) I claim that this schema is not properly indexed…memory. Each row is on the order of 30 bytes in the primary …disk. Perhaps you could verify that by watching IOstat. Tim’s measurements (… The correct URL for version you used is http://tokutek.com/downloads/iiBench…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… looked at the “ORDER BY OPTIMIZATION” section in MySQL manual but could not find a reason why MySQL does not use index in the following scenario: CREATE TABLE table1 ( id bigint(20) NOT NULL AUTO… to use an index but it does not. I just see this note: With EXPLAIN SELECT … ORDER BY, you can check whether MySQL can use indexes to…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… secondary index lookups, if the columns that are being fetched do not belong to the secondary index definition (and hence covering index optimization is not used… key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary… = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…used then, after the selected rows are read from table1, the values of indexed columns that will be used… > ’1995-03-09′ group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate …the best possible performance gain. This is not entirely visible in the manual either for…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… that, InnoDB locks all index records found by the WHERE clause with… to detect those gap locks using SHOW ENGINE INNODB STATUS: —TRANSACTION …index `GEN_CLUST_INDEX` of table `test`.`t` trx id 72C lock_mode X locks rec but not…reads (non repeatable reads) so in order to prevent problems with the…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 7 Comments
… making poor decision when it comes to choosing between using index for ORDER BY or using index for restriction. Consider we’re running web site which… restrictions and order by to be fully indexed. An extra problem comes from the fact MySQL prefers when it is possible to use index for…_id` int(10) unsigned NOT NULL, `seller_id` int(10) unsigned NOT NULL, `price` decimal(10,2) NOT NULL, KEY `cat_id` (`cat…
Post: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
… Performance Optimization is to avoid using functions when comparing constants or order by. Ie use indexed_col=N is good. function(indexed_col)=N is bad because MySQL Typically will be unable to use index on… constant by WHERE clause. If course it works for direct constants as well. However if functions are different MySQL is not able…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… about ORDER BY … LIMIT is – it provides scary explain statements and may end up in slow query log as query which does not use indexes, even if it is quite fast: mysql> explain select * from test order by k limit 5; +—-+————-+——-+——-+—————+——+———+——+———+——-+ | id…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… like ORDER BY A ; ORDER BY A,B ; ORDER BY A DESC, B DESC will be able to use full index for sorting (note MySQL may not select to use index… limit). However ORDER BY B or ORDER BY A, B DESC will not be able to use index because requested order does not line up with the order of data…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… still use second portion of the index for the order by. Ranges as well as IN lists make this optimization impossible, not even speaking about index… indexes and doing merge sort to get resulted rows in sorted order. Once this is implemented similar approach could be used for optimizing ORDER BY…
