Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.5.27, 5.3.8, 5.2.12, 5.1.62
-
None
-
None
-
all
Description
When using varchar/char type of index , there is excessive "Using where" in the result of explain command.
This cause unnecessary string comparison in query.
test case:
CREATE TABLE `tmp_xf_like` (
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
`user_id` bigint(20) NOT NULL,
|
`title` varchar(128) NOT NULL,
|
`memo` varchar(2000) DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
KEY `idx_userid` (`title`),
|
KEY `user_id` (`user_id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
|
|
|
mysql> explain select id from tmp_xf_like where title='a';
|
+----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+
|
| 1 | SIMPLE | tmp_xf_like | ref | idx_userid | idx_userid | 386 | const | 1 | Using where; Using index |
|
+----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+
|
1 row in set (0.01 sec)
|
The result is the same after changing field "title" into binary
But INT type looks normal.
explain select id from tmp_xf_like where user_id=1;
|
+----+-------------+-------------+------+---------------+---------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------------+------+---------------+---------+---------+-------+------+-------------+
|
| 1 | SIMPLE | tmp_xf_like | ref | user_id | user_id | 8 | const | 1 | Using index |
|
+----+-------------+-------------+------+---------------+---------+---------+-------+------+-------------+
|
1 row in set (0.00 sec)
|
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
When using varchar/char type of index , there is excessive "Using where" in the result of explain command. This cause unnecessary string comparison in query. test case: {noformat} CREATE TABLE `tmp_xf_like` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` bigint(20) NOT NULL, `title` varchar(128) NOT NULL, `memo` varchar(2000) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_userid` (`title`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; mysql> explain select id from tmp_xf_like where title='a'; +----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | tmp_xf_like | ref | idx_userid | idx_userid | 386 | const | 1 | Using where; Using index | +----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+ 1 row in set (0.01 sec) {noformat} The result is the same after changing field "title" into binary But INT type looks normal. {noformat} explain select id from tmp_xf_like where user_id=1; +----+-------------+-------------+------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | tmp_xf_like | ref | user_id | user_id | 8 | const | 1 | Using index | +----+-------------+-------------+------+---------------+---------+---------+-------+------+-------------+ 1 row in set (0.00 sec) {noformat} |
| Comment |
[ When using varchar/char type of index , there is excessive "Using where" in the result of explain command. This cause unnecessary string comparison in query. test case: CREATE TABLE `tmp_xf_like` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` bigint(20) NOT NULL, `title` varchar(128) NOT NULL, `memo` varchar(2000) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_userid` (`title`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; mysql> explain select id from tmp_xf_like where title='a'; +----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | tmp_xf_like | ref | idx_userid | idx_userid | 386 | const | 1 | Using where; Using index | +----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+ 1 row in set (0.01 sec) The result is the same after changing field "title" into binary But INT type looks normal. explain select id from tmp_xf_like where user_id=1; +----+-------------+-------------+------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | tmp_xf_like | ref | user_id | user_id | 8 | const | 1 | Using index | +----+-------------+-------------+------+---------------+---------+---------+-------+------+-------------+ 1 row in set (0.00 sec) ] |
| Affects Version/s | 5.2.12 [ 10702 ] | |
| Affects Version/s | 5.3.8 [ 10900 ] |
| Fix Version/s | 5.5.28 [ 11200 ] |
| Fix Version/s | 5.5.29 [ 11701 ] | |
| Fix Version/s | 5.5.28 [ 11200 ] |
| Fix Version/s | 5.5.29 [ 12102 ] | |
| Fix Version/s | 5.5.28a [ 11701 ] |
| Fix Version/s | 5.5.30 [ 11800 ] | |
| Fix Version/s | 5.5.29 [ 12102 ] |
| Fix Version/s | 5.5.31 [ 12700 ] | |
| Fix Version/s | 5.5.30 [ 11800 ] |
| Fix Version/s | 5.5.32 [ 13000 ] | |
| Fix Version/s | 5.5.31 [ 12700 ] |
| Fix Version/s | 5.5.33 [ 13300 ] | |
| Fix Version/s | 5.5.32 [ 13000 ] |
| Fix Version/s | 5.5.34 [ 13500 ] | |
| Fix Version/s | 5.5.33 [ 13300 ] |
| Fix Version/s | 5.5.34 [ 13700 ] | |
| Fix Version/s | 5.5.33a [ 13500 ] |
| Fix Version/s | 5.5.35 [ 14000 ] | |
| Fix Version/s | 5.5.34 [ 13700 ] |
| Fix Version/s | 5.5.36 [ 14600 ] | |
| Fix Version/s | 5.5.35 [ 14000 ] |
| Fix Version/s | 5.5.37 [ 15000 ] | |
| Fix Version/s | 5.5.36 [ 14600 ] |
| Fix Version/s | 5.5.38 [ 15400 ] | |
| Fix Version/s | 5.5.37 [ 15000 ] |
| Fix Version/s | 5.5.39 [ 15800 ] | |
| Fix Version/s | 5.5.38 [ 15400 ] |
| Workflow | defaullt [ 19729 ] | MariaDB v2 [ 44126 ] |
| Workflow | MariaDB v2 [ 44126 ] | MariaDB v3 [ 63396 ] |
| Workflow | MariaDB v3 [ 63396 ] | MariaDB v4 [ 139442 ] |