You can use the DESCRIBE SELECT .. command to find out more about index use.
You can try without the additional index and put the sess.test_run_id=2
condition in the JOIN condition.
Fred
----- Original Message -----
From: "Stephen Ince" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: 27 October 2004 11:43
Subject: [Hsqldb-developers] multi-column joins performance
I have a question regarding multi-column joins in hsqldb. I read the
documentation on indexes and queries but I am still getting slow
performance. Does anyone have any suggestion on how I can improve the
performance on the following query? I thought creating the index on the join
columns for the second table would help but it doesn't seem to work.
--Steve
I am using HSQLDB 1.7.2 release 5.
select count(*)
from sess join page_view
on sess.test_run_id = page_view.test_run_id
and sess.session_id = page_view.session_id
where sess.test_run_id = 2
--------------------------------
8000 rows -- 8 seconds.
select count(*)
from sess
where sess.test_run_id = 2
-------------------------------
2000 rows -- .5 seconds
select count(*)
from page_view
where test_run_id = 2
----------------------------------
8000 rows -- .5 seconds.
Schema
----------------------------------------
create cached table sess (
test_run_id int not null,
session_id int not null,
:
:
PRIMARY KEY( test_run_id, session_id)
) ;
create cached table page_view (
test_run_id int not null,
page_view_id int not null,
session_id int not null,
:
PRIMARY KEY(test_run_id, page_view_id)
) ;
create index page_view_idx ON page_view( test_run_id, session_id);
-------------------------------------------------------
This SF.Net email is sponsored by:
Sybase ASE Linux Express Edition - download now for FREE
LinuxWorld Reader's Choice Award Winner for best database on Linux.
http://ads.osdn.com/?ad_id=5588&alloc_id=12065&op=click
_______________________________________________
hsqldb-developers mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
-------------------------------------------------------
This SF.Net email is sponsored by:
Sybase ASE Linux Express Edition - download now for FREE
LinuxWorld Reader's Choice Award Winner for best database on Linux.
http://ads.osdn.com/?ad_id=5588&alloc_id=12065&op=click
_______________________________________________
hsqldb-developers mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
