|
This script:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (10),(20),(30),(40),(50);
|
EXPLAIN SELECT * FROM t1 WHERE a<>10;
|
EXPLAIN SELECT * FROM t1 WHERE 10<>a;
|
returns:
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | index | a | a | 5 | NULL | 5 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
...
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | index | NULL | a | 5 | NULL | 5 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
Notice, "possible_keys" is different in the two EXPLAINs.
"Not equal" operations is symmetric, so the expected results should the the same for the two EXPLAINs.
Another SQL script demonstrating the same problem:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a INT, b INT, KEY(a)) ENGINE=MyISAM;
|
INSERT INTO t1 (a) VALUES (10),(10),(10),(10),(10),(10),(10),(10),(10),(10),(70);
|
EXPLAIN SELECT * FROM t1 WHERE a<>10;
|
EXPLAIN SELECT * FROM t1 WHERE 10<>a;
|
The first explain returns:
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 3 | Using index condition |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|
The second returns:
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 11 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
{code}
|
|
|