List:Internals« Previous MessageNext Message »
From:Jay Pipes Date:February 11 2009 4:27am
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  
Ken Jacobs wrote:
> 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:

Sure, because InnoDB has a clustered organization. But the original 
poster says they do not store on disk in PK order...

And, yes, I know InnoDB doesn't store on disk in PK order, but the pages 
are arranged on disk in proximity.

>   SELECT * FROM table WHERE <primary key> BETWEEN a AND b;
> 
> The clustered index InnoDB uses is a Good Thing!

Yes, definitely a bonus for these types of queries! :)

> 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! ;-)

hehe, I think Ron works at ScaleDB, so probably won't happen ;)

-j

> 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