On 26 Sep 2002 at 11:17, Mario Weilguni wrote:
> On 26 Sep 2002 at 14:05, Shridhar Daithankar wrote:
> > Some time back I posted a query to build a site with 150GB of database. In
> last
> > couple of weeks, lots of things were tested at my place and there are some
> > results and again some concerns.
>
> > 2) Creating index takes huge amount of time.
> > Load time: 14581 sec/~8600 rows persec/~ an MB of data per sec.
> > Create unique composite index on 2 char and a timestamp field: 25226 sec.
> > Database size on disk: 26GB
> > Select query: 1.5 sec. for approx. 150 rows.
>
> I never tried 150GB of data, but 10GB of data, and this worked fine for me.
> Maybe it will help if you post your table schema, including which indexes you
> use, and the average size of one tuple.
Well the test runs were for 10GB of data. Schema is attached. Read in fixed
fonts..Last nullable fields are dummies but may be used in fututre and varchars
are not acceptable(Not my requirement). Tuple size is around 100 bytes..
The index creation query was
CREATE INDEX index1 ON tablename (esn,min,datetime);
What if I put datetime ahead? It's likely the the datetime field will have high
degree of locality being log data..
Bye
Shridhar
--
brain, v: [as in "to brain"] To rebuke bluntly, but not pointedly; to dispel a
source of error in an opponent. -- Ambrose Bierce, "The Devil's Dictionary"
Field Name Field Type Nullable Indexed
type int no no
esn char (10) no yes
min char (10) no yes
datetime timestamp no yes
opc0 char (3) no no
opc1 char (3) no no
opc2 char (3) no no
dpc0 char (3) no no
dpc1 char (3) no no
dpc2 char (3) no no
npa char (3) no no
nxx char (3) no no
rest char (4) no no
field0 int yes no
field1 char (4) yes no
field2 int yes no
field3 char (4) yes no
field4 int yes no
field5 char (4) yes no
field6 int yes no
field7 char (4) yes no
field8 int yes no
field9 char (4) yes no
