List:Internals« Previous MessageNext Message »
From:MARK CALLAGHAN Date:February 11 2009 3:48am
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 7:12 PM, Ron Hu <ron@stripped> 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.
>
> 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.

I think that HA_PRIMARY_KEY_IN_READ_INDEX does this. But it may imply
other things as well. If your table isn't clustered on the primary
key, then the plan that you want may do a lot of random IO and that
may be much slower than the plan that has a sort.

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