Topic: Hundreds of columns, index would be required on all of the
columns.

Hi!

How could this be implemented?

There are several items (or rows).

One item (or row) has e.g. even 900 columns.

An index would be needed on every column.

Would it be better to have 900 columns or should the table structure
be something else?

900 columns, table structure:
Item ID, item name, column 1, column 2, column 3, column 4, ...,
column 888, column 889, column 900
-> Index on all of the columns.
SQL clauses are very long with that kind of a table structure.

All of the columns could also be stored to one field too, but then
index would be hard or impossible? to be on all those "columns".

All columns in one field, table structure:
Item ID, item name, all columns in one field
-> How to have index on all of the columns in this case?

All of the 900 columns could be on separate rows too.
Then creation of an index might be easier. But size of the database
might increase.

I mean this, if all columns would be on separate rows. There would be
needed two tables, and two sample items to give an example.

Table 1:
Item ID, Item name
511, itemname511
512, itemname512

Table 2:
Item ID, column ID, value
511, 1, valueForColumn_1_forItem511
511, 2, valueForColumn_2_forItem511
511, 3, valueForColumn_3_forItem511
....
511, 899, valueForColumn_899_forItem511
511, 900, valueForColumn_900_forItem511
512, 1, valueForColumn_1_forItem512
512, 1, valueForColumn_1_forItem512
512, 2, valueForColumn_2_forItem512
512, 3, valueForColumn_3_forItem512
....
512, 899, valueForColumn_899_forItem512
512, 900, valueForColumn_900_forItem512

-> E.g. only one index would be created somehow on table 2 to be able
to make queries based on the values.

Maybe some extra columns would even be added/needed, I do not know, to
be able to make queries based on the values. How would the index would
be needed to be created? Would some extra column be needed etc.?


Some of the items can have different amount of columns. All do not
have 900 columns. So, the amount of columns varies.

Size of Item ID can be big -> It increases the size of the DB with the
latest table structures.
There can be a lot of Items. Maybe millions or tens of millions (or
more?).

How could this problem be solved?

What different possibilities there are in different DBMSs?

Also compression of data would be good. Some DBMSs have data
compression: compression of indexes and/or tables. Compression matters
at least a little too.

On Tue, 6 May 2008 00:05:09 -0700 (PDT), rock_sand81 (AT) yahoo (DOT) com wrote:

The database should definitely be of a different structure. Have a
look at :
http://en.wikipedia.org/wiki/Database_normalization


This will cause performance problems down the track.

[snip 2 end]

On Tue, 6 May 2008 00:05:09 -0700 (PDT), rock_sand81 (AT) yahoo (DOT) com wrote:

The database should definitely be of a different structure. Have a
look at :
http://en.wikipedia.org/wiki/Database_normalization


This will cause performance problems down the track.

[snip 2 end]

On Tue, 6 May 2008 00:05:09 -0700 (PDT), rock_sand81 (AT) yahoo (DOT) com wrote:

The database should definitely be of a different structure. Have a
look at :
http://en.wikipedia.org/wiki/Database_normalization


This will cause performance problems down the track.

[snip 2 end]

rock_sand81 (AT) yahoo (DOT) com wrote:

To what purpose?
Is that a data warehouse?

If it is, I suppose you would have told us.
If it isn't, you don't need 900 columns.

I don't have the docs at hand for other DBs, but postgres supports 32
indexes per table, mysql 16. Since these limits are way below your
needs, this means most people don't need as many indexes.

I would probably go for a hierarchical DB. Since your structure is
probably heavily de-normalized, it's unlikely you need advanced RDBMS
features anyway.

Also: are you positively sure you need to retrieve data by any of those
indexes? What's the cardinality (# of different values) of each of these
columns, for instance?

rock_sand81 (AT) yahoo (DOT) com wrote:

To what purpose?
Is that a data warehouse?

If it is, I suppose you would have told us.
If it isn't, you don't need 900 columns.

I don't have the docs at hand for other DBs, but postgres supports 32
indexes per table, mysql 16. Since these limits are way below your
needs, this means most people don't need as many indexes.

I would probably go for a hierarchical DB. Since your structure is
probably heavily de-normalized, it's unlikely you need advanced RDBMS
features anyway.

Also: are you positively sure you need to retrieve data by any of those
indexes? What's the cardinality (# of different values) of each of these
columns, for instance?

rock_sand81 (AT) yahoo (DOT) com wrote:

To what purpose?
Is that a data warehouse?

If it is, I suppose you would have told us.
If it isn't, you don't need 900 columns.

I don't have the docs at hand for other DBs, but postgres supports 32
indexes per table, mysql 16. Since these limits are way below your
needs, this means most people don't need as many indexes.

I would probably go for a hierarchical DB. Since your structure is
probably heavily de-normalized, it's unlikely you need advanced RDBMS
features anyway.

Also: are you positively sure you need to retrieve data by any of those
indexes? What's the cardinality (# of different values) of each of these
columns, for instance?

rock_sand81 (AT) yahoo (DOT) com wrote:

Oh, I missed this sentence at first.

This gives me a clue about your problem, hence I can say: go for it.
Trade your columns with rows and you'll be much happier.

Don't worry about the size of the DB. Less indexes, less space, anyway.

rock_sand81 (AT) yahoo (DOT) com wrote:

Oh, I missed this sentence at first.

This gives me a clue about your problem, hence I can say: go for it.
Trade your columns with rows and you'll be much happier.

Don't worry about the size of the DB. Less indexes, less space, anyway.

rock_sand81 (AT) yahoo (DOT) com wrote:

Oh, I missed this sentence at first.

This gives me a clue about your problem, hence I can say: go for it.
Trade your columns with rows and you'll be much happier.

Don't worry about the size of the DB. Less indexes, less space, anyway.