List:Internals« Previous MessageNext Message »
From:Ron Hu Date:February 11 2009 6:08pm
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  
Thanks Mark for advice.  Yes, it may be faster to fetch records with full
table scan first and then perform sorting.  Full table scan is quite
effective in reducing disk seek time caused by random IO.

  -Ron

> -----Original Message-----
> From: MARK CALLAGHAN [mailto:mdcallag@stripped]
> Sent: Tuesday, February 10, 2009 7:48 PM
> To: ron@stripped
> Cc: internals@stripped
> Subject: Re: How to make MySQL query processor decide to use index access
> to fetch table records in primary key order?
> 
> 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
> 
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    http://lists.mysql.com/internals?unsub=1
> 


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