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

[Ken Jacobs]

Actually, two things are true in general ...

1. You cannot always rely on avoiding a sort when ORDER BY is done.  
Relational databases maintain data in sets, not ordered lists.

2. With InnoDB, if you retrieve rows in based a range of primary key 
order, it does NOT require a lot of random i/o ...the InnoDB clustered 
index will mean that rows with close values of PKs will be stored (and 
retrieved) together.  Thus, this query is rather efficient when using 
InnoDB:

   SELECT * FROM table WHERE <primary key> BETWEEN a AND b;

The clustered index InnoDB uses is a Good Thing!

Now, how the MySQL optimizer determines the access path ... well, that 
is a different matter.  But you might try using InnoDB to see what 
happens! ;-)

Ken

Jay Pipes wrote:
> 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
>>
>>
>>
>>
>
>