From: MARK CALLAGHAN Date: February 11 2009 3:48am Subject: Re: How to make MySQL query processor decide to use index access to fetch table records in primary key order? List-Archive: http://lists.mysql.com/internals/36244 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit On Tue, Feb 10, 2009 at 7:12 PM, Ron Hu wrote: > Suppose a table t1 has all its records inserted randomly (that is they are > not in the primary key order). Now we want to execute this query: > > SELECT * FROM t1 ORDER BY primary_key; > > The fast way to fetch all table records in primary key order is to use > primary key. This way can AVOID file sorting after fetching all table > records. > > However, MySQL query processor decides to use full table scan by calling > method rnd_init() and rnd_next(). How can I make MySQL query processor > decide to use index access by calling method index_read and index_next? Any > pointers will be appreciated. I think that HA_PRIMARY_KEY_IN_READ_INDEX does this. But it may imply other things as well. If your table isn't clustered on the primary key, then the plan that you want may do a lot of random IO and that may be much slower than the plan that has a sort. > > The table_flags() of my storage engine is defined as: > > Table_flags table_flags() const { > return (HA_REC_NOT_IN_SEQ | > HA_NULL_IN_KEY | > HA_CAN_INDEX_BLOBS | > HA_CAN_SQL_HANDLER | > HA_BINLOG_ROW_CAPABLE | > HA_BINLOG_STMT_CAPABLE | > HA_CAN_GEOMETRY | > HA_TABLE_SCAN_ON_INDEX | > HA_FILE_BASED | > HA_HAS_RECORDS > ); > } > > > > Thanks. > > -Ron > > > > > -- > MySQL Internals Mailing List > For list archives: http://lists.mysql.com/internals > To unsubscribe: http://lists.mysql.com/internals?unsub=mdcallag@stripped > > -- Mark Callaghan mdcallag@stripped