I have a query which joins three tables, the detail for the tables are as below:
Table no1: 81 columns, 2 million rows
Table no2: 28 columns, 200K
Table no3: 37 columns, 300K
For first execution of my query, I ran the query without using any PRIMARY KEY constraint, or even defining the ID columns as PRIMARY KEY for any of the tables, thus the primary key was set by CRDB as “rowid” for all three tables, and the execution time was about 45 seconds.
But for the second execution of the query, I dropped all of the tables, redefined all three tables with the ID columns as the PRIMARY KEYs, loaded my data into the tables.
For both executions of the query, after loading the data into the tables, an index was created on ID columns of all three tables.
I was expecting the to get much less execution time after setting the primary key and redefining the tables, but the execution time for second time of running the query was almost the same as first time, about 40 seconds.
Am I doing anything wrong? or this is the best I can get, as anyway there was an PRIMARY KEY associated to the tables, for first case it was assigned by CRDB as “rowid”, and the second time I defined it manually at the time of creating the tables.