List:Internals« Previous MessageNext Message »
From:Ken Jacobs Date:February 11 2009 4:34am
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  
Not to quibble, Jay, but the original poster didn't say what storage 
engine he was using.  He said the rows were not inserted in order, but 
randomly.

Given that Ron works for ScaleDB, maybe he should consider implementing 
primary key-based clustered indexes!  :-)

heh-heh!

See you all in April at the UC!

Ken

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