MySQL Bugs: #37911: Force Index does not work on Falcon table

Bug #37911	Force Index does not work on Falcon table
Submitted:	7 Jul 2008 1:56	Modified:	8 Jul 2008 1:25
Reporter:	Xuekun Hu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S5 (Performance)
Version:	6.0	OS:	Linux (SLES10SP1 )
Assigned to:	Sergey Petrunya	CPU Architecture:	Any
Tags:	falcon, Optimizer

Description:
Force Index does not work on Falcon table.

explain select id from test force index (primary);

+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

How to repeat:
create table test (
    id int,
    c  char(10),
    primary key (id)
) engine=falcon;

insert into test value (1, "a"), (2, "b"), (3, "c");

explain select id from test force index (primary);

Thank you for a problem report. Verified with 6.0.5. FORCE INDEX works in this case for InnoDB and MyISAM tables, but not for Falcon.

Is this a Falcon bug?  Storage engines don't get much choice
about how they get records.

FORCE INDEX is an instruction to the optimizer to prefer plans that make use
of the specified index. If there are no possible plans that would use the
specified index, it won't be used. 

Falcon doesn't support "index only", so there is no meaningful way we could 
use an index to resolve the specified query. That's why you get full table 
scan despite the FORCE INDEX hint.

To check if FORCE INDEX works with Falcon, let's consider a case where
 * there is index I that can be used to answer the query 
 * but it's not what the optimizer would pick by default.

create table tf1(a int, b int, key(a), key(b)) engine=Falcon;

insert into tf1 values (1,1);
insert into tf1 values (2,2);
insert into tf1 values (3,3);
insert into tf1 values (4,4);

explain select * from tf1 where a< 5 and b < 5\G
explain select * from tf1 force index(b)  where a< 5 and b < 5\G
mysql> explain select * from tf1 where a< 5 and b < 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tf1
         type: range
possible_keys: a,b
          key: a
      key_len: 5
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set (0.00 sec)

### The optimizer would pick index `a`.

mysql> explain select * from tf1 force index(b)  where a< 5 and b < 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tf1
         type: range
possible_keys: b
          key: b
      key_len: 5
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set (0.01 sec)

### We can use FORCE INDEX to make it use  index `b` instead.

The conclusion is that FORCE INDEX does work on Falcon tables. Inability to do 'index only' reads is a known limitation (orthogonal to FORCE INDEX handling).

Considering the above, changing status to Not a Bug.

Hi, Sergey

How do you explain the example that I gave? 
Or does that mean if no where clause, Falcon is not able to do "index only" scan?

Thx, Xuekun

Hi Xuekun,

> Or does that mean if no where clause, Falcon is not able to do "index
only" scan?

At the moment, we have this property:

  Falcon storage engine does not support "index only" scans.  (1)

the presence or lack of WHERE clause does not matter. The reason behind 
limitation (1) is that Falcon needs to look into table record to check if
the record is visible to the current transaction. It cannot tell this from
just looking at the index tuple.

Let me expand on the "no index only" issue a bit
further.  Falcon's indexes are multi-generational -
they contain entries for all versions of each record.
Thus, the fact that there is an 'a' in the index
does not mean that the current transaction has found
a hit on 'a'.  The system has to make an extra check
to insure that the 'a' in question belongs to a
version of the record that this transaction can see.

Falcon indexes do not keep transaction information.
Maria does.  There's a trade-off between the performance
gain of index only retrievals and the size of the index.
Maria took that trade-off in favor of index only retrievals.
Falcon chose smaller, simpler indexes that should improve
performance on all queries.