List:Internals« Previous MessageNext Message »
From:MARK CALLAGHAN Date:February 11 2009 4:41am
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  
On Tue, Feb 10, 2009 at 8:10 PM, Jay Pipes <Jay.Pipes@stripped> 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.

And all of that is true as long as we mean the new-style external
sort. The old-style external sort sorts (key,row-pointer) pairs and
then fetches the other columns when the sort is done. The old-style
sort can be faster in some cases, but is much less predictable.

>
>> 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
>>
>>
>>
>>
>
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    http://lists.mysql.com/internals?unsub=1
>
>



-- 
Mark Callaghan
mdcallag@stripped
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