Another question: why is type='range' for sort_intersect? We have
type=index_merge for intersect and [sort]_union, so it seems rather confusing
that we have type=range for sort_intersect?
----- Forwarded message from Sergey Petrunya -----
Date: Wed, 19 Jan 2011 11:11:14 +0300
From: Sergey Petrunya
To: igor@askmonty.org
Cc: Maria Developers
Subject: No "Using index" support in sort-intersect - intentional?
Hello Igor,
I was looking at sort-intersect and noticed the following: Consider this table:
CREATE TABLE `t1` (
`k1` int(11) DEFAULT NULL,
`k2` int(11) DEFAULT NULL,
`filler` char(100) DEFAULT NULL,
KEY `k1` (`k1`),
KEY `k2` (`k2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Let's try a sort-intersect query:
MariaDB [timerge1]> EXPLAIN select 1 from t1 where k1<30 and k2 < 30\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: range
possible_keys: k1,k2
key: k1,k2
key_len: 5,5
ref: NULL
rows: 1
Extra: Using sort_intersect(k1,k2); Using where
1 row in set (0.01 sec)
The query doesn't use any columns not covered by the used indexes, however, we don't see "using index".
Analogous ROR-intersect query will use "Using index":
MariaDB [timerge1]> EXPLAIN select 1 from t1 where k1=30 and k2 = 30\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index_merge
possible_keys: k1,k2
key: k1,k2
key_len: 5,5
ref: NULL
rows: 1
Extra: Using intersect(k1,k2); Using where; Using index
1 row in set (0.00 sec)
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
----- End forwarded message -----
--
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
