thanx.
----- Original Message ----- From: "fredt" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, October 29, 2004 11:55 AM
Subject: Re: [Hsqldb-developers] Re: multi-column joins performance
Just for reference, since 1.7.2, joins on multiple columns can use all the
columns of a multi-column index. See the changes I made to theTableFilter
and Index classes. It is still not perfect, especially when it selects which
index to use among those available.
Fred
----- Original Message ----- From: "Thomas Mueller" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: 29 October 2004 16:16
Subject: [Hsqldb-developers] Re: multi-column joins performance
Hi, I think the developers mailing list is not exactly the right place to discuss this, the help forum would be better?
A known limitation of HSQLDB is the index usage. If I'm correct, HSQLDB
really only uses the first column of the index. So if you have a bad
selectivity on the first column (that is, high number of rows but only a few
distinct values), it would be better to use another index on the second
column and make sure HSQLDB actually uses the second index. In your case, it
seems selectivity on test_run_id is indeed low (2000 / 8000 rows with the
same value).
I don't know your data, but if session_id has a higher selectivity (more distinct values), then try this:
create index page_view_idx ON page_view(session_id);
select count(*) from sess join page_view on sess.session_id = page_view.session_id and sess.test_run_id = page_view.test_run_id where sess.test_run_id = 2 -- reverse the join condition to make sure the new index is used
Tell me your results. (Note to self: index behaviour should be better documented)
Thomas
-- NEU +++ DSL Komplett von GMX +++ http://www.gmx.net/de/go/dsl GMX DSL-Netzanschluss + Tarif zum supergünstigen Komplett-Preis!
------------------------------------------------------- This Newsletter Sponsored by: Macrovision For reliable Linux application installations, use the industry's leading setup authoring tool, InstallShield X. Learn more and evaluate today. http://clk.atdmt.com/MSI/go/ins0030000001msi/direct/01/ _______________________________________________ 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
------------------------------------------------------- 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_idU88&alloc_id065&op=click _______________________________________________ hsqldb-developers mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
