Re: Hundreds of columns, index on all of those.

rock_sand81@[EMAIL PROTECTED]
 wrote:

> The size of the DB would increase quite a lot, because each field /
> column ID would need Item ID.

I meant the total size of indexes. I expect 900 indexes would take up 
more size than 1 huge index on the same data.

I still cannot fathom the application domain you're working in, and I 
suspect no specific suggestions can be made without further 
understanding the requirements.

Anyway, with DBs, performance issues are often non-trivial to understand .
In your case you should really start with a normalized schema. Then, 
after testing and probably analyzing some query throgh the optimizer, 
see if you need to change/partition/whatever your indexes.

Say, partitioning the data by the group ID could be a way to.. to what? 
I don't know, it could be helpful or not. Needs to be tested.

I suggest, for instance, The Art Of SQL (google for it) as it covers 
several issues with indexing.

> Would it be wise (or stupid) to add the group ID also to Table 2 to
> make the query fast?

It would probably be useful. But you need to test.

>> hence I can say: go for it.
>> Trade your columns with rows and you'll be much happier.
> 
> That might be the only possiblity.

Also, did you tell which DBMS you're using?