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

[Jay Pipes]

Ken Jacobs wrote:
> See you all in April at the UC!

Most definitely! :)

Cheers,

Jay

> 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
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>