Description:
With index,
mysql> CREATE TABLE t1 (
-> mychar char(20) DEFAULT NULL,
-> KEY mykey (`mychar`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=binary;
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> insert into t1 values (1), (2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT mychar FROM t1 WHERE ( ( mychar) BETWEEN ( 'd' ) AND (7 ) );
Empty set (0.00 sec)
Without index,
mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> CREATE TABLE t1 (
-> mychar char(20) DEFAULT NULL
-> #KEY mykey (`mychar`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=binary;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> insert into t1 values (1), (2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT mychar FROM t1 WHERE ( ( mychar) BETWEEN ( 'd' ) AND (7 ) );
+----------------------+
| mychar |
+----------------------+
| 1 |
| 2 |
+----------------------+
2 rows in set, 4 warnings (0.00 sec)
How to repeat:
-- with index
drop table if exists t1;
CREATE TABLE t1 (
mychar char(20) DEFAULT NULL,
KEY mykey (`mychar`)
) ENGINE=InnoDB DEFAULT CHARSET=binary;
insert into t1 values (1), (2);
SELECT mychar FROM t1 WHERE ( ( mychar) BETWEEN ( 'd' ) AND (7 ) );
-- without index
drop table if exists t1;
CREATE TABLE t1 (
mychar char(20) DEFAULT NULL
#KEY mykey (`mychar`)
) ENGINE=InnoDB DEFAULT CHARSET=binary;
insert into t1 values (1), (2);
SELECT mychar FROM t1 WHERE ( ( mychar) BETWEEN ( 'd' ) AND (7 ) );
Description: With index, mysql> CREATE TABLE t1 ( -> mychar char(20) DEFAULT NULL, -> KEY mykey (`mychar`) -> ) ENGINE=InnoDB DEFAULT CHARSET=binary; Query OK, 0 rows affected (0.09 sec) mysql> mysql> insert into t1 values (1), (2); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT mychar FROM t1 WHERE ( ( mychar) BETWEEN ( 'd' ) AND (7 ) ); Empty set (0.00 sec) Without index, mysql> drop table if exists t1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> CREATE TABLE t1 ( -> mychar char(20) DEFAULT NULL -> #KEY mykey (`mychar`) -> ) ENGINE=InnoDB DEFAULT CHARSET=binary; Query OK, 0 rows affected (0.05 sec) mysql> mysql> insert into t1 values (1), (2); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT mychar FROM t1 WHERE ( ( mychar) BETWEEN ( 'd' ) AND (7 ) ); +----------------------+ | mychar | +----------------------+ | 1 | | 2 | +----------------------+ 2 rows in set, 4 warnings (0.00 sec) How to repeat: -- with index drop table if exists t1; CREATE TABLE t1 ( mychar char(20) DEFAULT NULL, KEY mykey (`mychar`) ) ENGINE=InnoDB DEFAULT CHARSET=binary; insert into t1 values (1), (2); SELECT mychar FROM t1 WHERE ( ( mychar) BETWEEN ( 'd' ) AND (7 ) ); -- without index drop table if exists t1; CREATE TABLE t1 ( mychar char(20) DEFAULT NULL #KEY mykey (`mychar`) ) ENGINE=InnoDB DEFAULT CHARSET=binary; insert into t1 values (1), (2); SELECT mychar FROM t1 WHERE ( ( mychar) BETWEEN ( 'd' ) AND (7 ) );