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

I think it's ok. If you want you could use the EXPLAIN statement to
check which order of index is better (A,B,C),(B,A,C), (C,B,A) or
(B,C,A), etc... Take note of the '2 rows in set (0.04 sec)' from the
sample. You could probably consider the duration as a part of your testing.

Try to fill the table with records (start with 10 records, then
increment it by 10) so you could have better results with the EXPLAIN
statement. It's better to test with different number of records to
really see the changes in duration.

Sample EXPLAIN statement
(http://dev.mysql.com/doc/refman/5.0/en/explain.html)

mysql> EXPLAIN SELECT a,b FROM t1 WHERE a<2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: a
          key: a
      key_len: 5
          ref: NULL
         rows: 2
        Extra: Using where with pushed condition
2 rows in set (0.04 sec)


- Mic


Mark Smith wrote:
> 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

-----BEGIN PGP SIGNATURE-----
Comment: GPG Public Key: https://www.biglumber.com/x/web?qs=0x4A256EC8
Comment: GPG Public Key: http://www.lorztech.com/GPG.txt
Comment: Google, Skype, Yahoo ID: michaellouieloria

iQEVAwUBRQeXxbXBHi2y3jwfAQqiAAf/SdcyH6IJulF8yEmXGzAogqKevJv2cY4I
CJK++6m1CNjmmII3ZyEhW+1Af03ncEK8B2e3QjPmNq91SthNgMV4aXud61pcVTBe
9B6aYVURqal2WGKcFnj+Bb6CIPfzc3UAIjIl7qhoUbIuryhioJDHFbMhjctJJjIQ
QGcuuXOz8O2fUn3PShSpfCAK7RGT+buxZ0Gq0OTCZOXNIp6xxROYzFRef7Kg9k08
4cpoJPr9ED/8oFW0RjOOMGrJc78Q0C8KoCOYN1f/wJm+dxvQrg/yg3JZeJelnBCG
5UvMQXMBhIT7QcTi7u+9MQZ0WxAvVDmtojLN//C5lh/1Ys3SpHICmA==
=xn5y
-----END PGP SIGNATURE-----