RE: Multi-Column Indexes, field order, and performance.

Thank you Michael,

I am a where of the limitations that you outline.  My question though if
all of your queries contain WHERE conditions (with a, b, and c) then
does it matter if the index is (on c, b, a)?

Example

Select # different types of SQL
FROM silly_table
WHERE
  a = 1 and # could be any value
  b = 2 and # could be any value
  c >= '2006-09-01 00:00:00' and c < '2006-09-01 00:00:00';
 # could be any time range

The silly table might look like:
CREATE TABLE silly_table (
a float NOT NULL default 0,
b float NOT NULL default 0,
c DateTime NOT NULL default '0000-00-00 00:00:00',
# Now here is the question does it matter which one I create?
# KEY my_key(a,b,c),
# KEY my_key(c,b,a),
# KEY my_key (any combination there of?),
) Engine=MyISAM;

Thanks,

Mark

-----Original Message-----
From: Michael Louie Loria [mailto:[email protected]] 
Sent: Tuesday, September 12, 2006 6:42 PM
To: [email protected]
Subject: Re: Multi-Column Indexes, field order, and performance.

> Subject: Multi-Column Indexes, field order, and performance.
> From: "Mark Smith" <[email protected]>
> Date: Thu, 7 Sep 2006 10:49:27 -0700
> To: <[email protected]>
> 
> 
> Hello I have a question on using a Index that spans multiple fields
> INDEX(a,b,c).
> 
>  
> 
> If my queries, that use this index, all have where conditions that
> include all three fields; does it matter about the index order for
query
> performance?
> 
>  
> 
> In other words is INDEX(a,b,c) = INDEX(c,b,a) or any combination there
> in?  Is it true for all index types or Databases for that matter
(MySQL,
> Oracle, MSSQL)?
> 
>  
> 
> I am in a quandary because the last component in this case is time
> (field c).  I have been asked to aggregate across the time dimension
for
> fields a and b.  Because now a and b are no longer conditions in the
> where statement (now included in the group by) using the existing
index
> is no longer possible or feasible.  Because of legacy SQL code I am
> worried about simply changing the order at this time.  Adding a second
> index is frowned upon due to the huge nature of the table and the
space
> requirements.

The index order matters.

Example: INDEX(A,B,C)

You can use that index to search on (A) or (on A and B) or (on A,B and
C), but you cannot use it to search on C. Given this flexibility,
organize the columns in the index in an order that will support the
widest range of queries. Remember that you can rarely afford to support
all possible indexes because of the overhead indexes add to DML
operations so make sure you pick the most effective set of indexes.



-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]