|
|
| Тема |
 mysql multiple column index |
|
| Автор |
unF (.)(.) |
|
| Публикувано | 28.04.05 12:34 |
|
|
|
Това е таблицата:
CREATE TABLE `geo` (
`ipFrom` int(10) unsigned zerofill NOT NULL default '0000000000',
`ipTo` int(10) unsigned zerofill NOT NULL default '0000000000',
`Country` varchar(64) NOT NULL default '',
`State` varchar(128) NOT NULL default '',
`City` varchar(128) NOT NULL default '',
PRIMARY KEY (`ipTo`,`ipFrom`)
) TYPE=MyISAM;
а това е случая:
mysql> explain SELECT * FROM geo WHERE ipTo >= 3645610104 AND ipFrom <= 3645610104;
+----------+-------+---------------+---------+---------+------+-------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------+-------+---------------+---------+---------+------+-------+-------------+
| geo | range | PRIMARY | PRIMARY | 4 | NULL | 41914 | Using where |
+----------+-------+---------------+---------+---------+------+-------+-------------+
1 row in set (0.00 sec)
mysql> explain SELECT * FROM geo WHERE ipTo >= 1108966761 AND ipFrom <= 1108966761;
+----------+------+---------------+------+---------+------+---------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------+------+---------------+------+---------+------+---------+-------------+
| geo | ALL | PRIMARY | NULL | NULL | NULL | 1601606 | Using where |
+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
защо при първата заявка използва индекс, а при втората не при положение че са еднакви?:)
unF .. 
| |
| |
| 
|
|
