Michael Stassen wrote:
Sort of. The order of requirements in the WHERE clause is irrelevant.
...
No composite index will be fully used here. MySQL uses composite
indexes from left to right, *stopping on the first key part used in a
range* rather than to match a constant. "WHERE latitude > 39" is a
range, so the composite index on (latitude, longitude, link_id) will be
no better than a single column index on latitude.
>
Michael
Thanks for the corrections, Michael :)
My apologies for posting wrong info to the list
For my own understanding of this, I created a simple table...
CREATE TABLE `test` (
`a` int(11) NOT NULL default '0',
`b` int(11) NOT NULL default '0',
`c` int(11) default NULL,
KEY `a` (`a`,`b`,`c`)
);
and inserted about a dozen random integers, then checked the output of
EXPLAIN for two queries, one using a range on all three columns, the
other using a constant for the first column...
mysql> explain select * from test where a > 30 and b > 30 and c > 30;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| 1 | SIMPLE | test | range | a | a | 4 |
NULL | 13 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
mysql> explain select * from test where a = 30 and b > 30 and c > 30;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| 1 | SIMPLE | test | range | a | a | 8 |
NULL | 1 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
The only difference is that in the second query, key_len is 8 bytes, not
4. Makes sense, since as you say, each query only uses the left-most
part of the index, stopping if a range is encountered.
Thanks again,
Devananda vdv
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
