Hi, Are you saying that these columns are not indexed? I guess I'm missing something... I matched these tables in MySQL and it took less than a second to find those 100 records (although using B-tree index).... So how do you run fast queries when you have to run them against multiple tables? Could you please help me on this one? Thanks. Dariusz.
Martin Kersten wrote:
dariuszs wrote:
Hi, Sure. t1 100 records t2 120,000,000 records
Assuming you have more than a few thousand different strings this amounts to a ca 3.6G string table that might have to be loaded into memory that's cost factor 1. Then the system probably decides on building a string hash (depending on query type), cost factor 2 and if you are unlucky there it may chase through the collision list Such decisions can be obtained with the --algorithms flag
So the 3 minutes should first be understood as a result of running to a 'cold' database or 'hot' database.
In case you want to identify the precise cost, it is worthwhile to run the SQL query with the trace option or with the debugger and trace flags.
regards, Martin
create table t1 (c1 int, key30 varchar(30)); create table t2 (c1 int, key30 varchar(30));
update t1 set c1=(select c1 from t2 where t2.key30=t1.key30);
To find 100 records with 30 bytes key takes about 3 minutes on 64bit Windows with 48GB memory and 2 Xenon CPUs, compared to other counts that I can do with this database that very, very slow. Thanks. Dariusz.
Niels Nes wrote:
On Thu, Mar 26, 2009 at 01:42:59PM -0400, dariuszs wrote:
Hi, I've got 2 tables with 30 bytes varchar keys and matching those tables takes forever, eg. find 10 records from one table in another. Can you help with that? Thanks. Dariusz.
we would need more info for this, ie could you post your table definition and query?
Niels
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users