List:Internals« Previous MessageNext Message »
From:Ron Hu Date:February 11 2009 3:12am
Subject:How to make MySQL query processor decide to use index access to fetch table records in primary key order?
View as plain text  
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.

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



Thread
How to make MySQL query processor decide to use index access to fetch table records in primary key order?Ron Hu11 Feb
  • Re: How to make MySQL query processor decide to use index access to fetch table records in primary key order?MARK CALLAGHAN11 Feb
    • RE: How to make MySQL query processor decide to use index access to fetch table records in primary key order?Ron Hu11 Feb
  • Re: How to make MySQL query processor decide to use index access tofetch table records in primary key order?Jay Pipes11 Feb
    • Re: How to make MySQL query processor decide to use index accessto fetch table records in primary key order?Ken Jacobs11 Feb
      • Re: How to make MySQL query processor decide to use index access tofetch table records in primary key order?Jay Pipes11 Feb
        • Re: How to make MySQL query processor decide to use index accessto fetch table records in primary key order?Ken Jacobs11 Feb
          • Re: How to make MySQL query processor decide to use index access tofetch table records in primary key order?Jay Pipes11 Feb
    • Re: How to make MySQL query processor decide to use index access to fetch table records in primary key order?MARK CALLAGHAN11 Feb