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