List:MySQL on Win32« Previous MessageNext Message »
From:Paul DuBois Date:April 5 2001 8:08pm
Subject:Re: question on leftmost prefix
View as plain text  
On Thu, Apr 05, 2001 at 03:58:45PM -0400, Harkins wrote:
> When working with a multiple index of more than two columns, say the
> following:
> 
> Forename
> Surname
> Title
> 
> A leftmost prefix is formed as long as Forename is there.
> 
> That means a leftmost prefix includes
> 
> forename,surname,title
> forename,surname
> forename,title
> forename
> 
> Is this correct, or is the forename,title example not valid? If this is the

A prefix isn't a prefix if it skips columns.  See below.

> case, then what's the disclaimer for the rule "a leftmost prefix is formed
> as long as leftmost column is included." I'm a little confused on this
> issue. Thanks!
> 
> slh

When you create a multiple-column index (or key), MySQL can use the index
in a query if you specify values for all of the index columns.  However,
the index may also be used if you specify values only for some of its
columns, as long as they form some leading part of the key.  This is
called a leftmost prefix of the key.  For example, if you manufacture
a medical product that is packaged in individually numbered vials
that are grouped for sale into boxes, cases, and lots, you might have
a multiple-column index that comprises columns named lot, case, box,
and vial.  You gain the benefits of the index if you specify values for
all four columns in a query.  But the index is useful even if you specify
just lot, case, and box, to find all vials in a box.  In this case,
you've specified the leftmost three columns of the index.  Other leftmost
prefixes are lot and case, or just lot.  However, the combination of lot
and box do not form a leftmost prefix, because it skips the case column.
Thread
question on leftmost prefixHarkins5 Apr
  • Re: question on leftmost prefixPaul DuBois5 Apr
  • Re: question on leftmost prefixHarkins5 Apr