Hey there,
I've been playing with H2 1.4.195, trying to get an ordered resultsset from
a query that contains a join.
In the plain vanilla case, H2 tends to create a resultset containing
everything from the join plus the sort columns and then sort the set in
memory. Here's an example for what I mean:
drop table A if exists;
drop table B if exists;
create table if not exists B (id bigint, a varchar, b varchar, c varchar,
primary key (id));
create table if not exists A (id bigint, b_id bigint, primary key (id),
foreign key (b_id) references B(id));
create index ab on B (a,b);
create index c on B (c);
So we have two tables A and B. A references B. Primary keys on the id
fields. A foreign key on the... well, foreign key.
Here's my typical query:
select A.b_id from A inner join B on A.b_id = B.id where A.id = 1 order by
B.a, B.b;
As mentioned above, I'm using columns B.a and B.b for sorting, but they are
not selected. When run with EXPLAIN, this is the result:
SELECT
A.B_ID
FROM PUBLIC.A
/* PUBLIC.PRIMARY_KEY_41_0: ID = 1 */
/* WHERE A.ID = 1
*/
INNER JOIN PUBLIC.B
/* PUBLIC.PRIMARY_KEY_4: ID = A.B_ID
AND ID = A.B_ID
*/
ON 1=1
WHERE (A.ID = 1)
AND (A.B_ID = B.ID)
ORDER BY =B.A, =B.B
The join is as efficient as possible, but the sort happens in memory, since
the "/* index sorted */" is missing and EXPLAIN does not mention the index
"ab", which could have been used.
So far so good. For some of my data the efficiency of the join is not
important (almost all rows from B are returned anyway). What's important to
me though is that an index is used for sorting.
So I've tried to use USE INDEX.
explain select A.b_id from A inner join B use index (ab) on A.b_id = B.id
where A.id = 1 order by B.a, B.b;
results in:
SELECT
A.B_ID
FROM PUBLIC.B USE INDEX (AB)
/* PUBLIC.AB */
INNER JOIN PUBLIC.A
/* PUBLIC.PRIMARY_KEY_41_0: ID = 1 */
ON 1=1
WHERE (A.ID = 1)
AND (A.B_ID = B.ID)
ORDER BY =B.A, =B.B
/* index sorted */
Awesome! That's what I wanted.
But now comes the part that's confusing to me. When I indicate that I want
to use index "c"—defined on the column B.c that has nothing to do with my
query whatsoever, H2 still uses the correct index (ab):
explain select A.b_id from A inner join B use index (c) on A.b_id = B.id
where A.id = 1 order by B.a, B.b;
SELECT
A.B_ID
FROM PUBLIC.B USE INDEX (C)
/* PUBLIC.AB */
INNER JOIN PUBLIC.A
/* PUBLIC.PRIMARY_KEY_41_0: ID = 1 */
ON 1=1
WHERE (A.ID = 1)
AND (A.B_ID = B.ID)
ORDER BY =B.A, =B.B
/* index sorted */
It seems to me, that the USE INDEX hint is disregarded a little, but it's
still not the same as without the hint, because magically the best index
(ab) is used. So that's kind of odd.
Is this by design? Is this a feature (because the best index is chosen)? Or
is this a bug?
Cheers,
-hendrik
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
