maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

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 <psergey@xxxxxxxxxxxx> -----

Date: Wed, 19 Jan 2011 11:11:14 +0300
From: Sergey Petrunya <psergey@xxxxxxxxxxxx>
To: igor@xxxxxxxxxxxx
Cc: Maria Developers <maria-developers@xxxxxxxxxxxxxxxxxxx>
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