Re: How to make MySQL query processor decide to use index access to fetch table records in primary key order?

[Jay Pipes]

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.  

Actually, this is typically the *slower* plan b/c it requires tons of 
random I/O.  The bottleneck typically won't be the sort.  It will be the 
I/O to do all the random reads from disk.  If a table scan can 
accomplish getting all the table's records into memory in a single (or 
very few) I/O operation, then the sort on those records in memory is a 
trivial cost compared to lots of random I/O.

> 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
> 
> 
> 
>