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

On May 6, 7:41 am, rock_san...@[EMAIL PROTECTED]
 wrote:
> > postgres sup****ts 32
> > indexes per table, mysql 16
>
> Do you have a source for that information?
> I could not quickly find that information.
>
> Or did you refer to the max amount of columns per one index?
> Those kinds of amounts are mentioned on this page (that is why I would
> double check this issue):http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL
> -> Multi-column Indexes
>
>
>
> > It would probably be useful. But you need to test.
>
> Yes it might be required.
>
> I will check the Art of SQL.
>
>
>
> > Also, did you tell which DBMS you're using?
>
> Oracle might be one. Maybe MySQL would be good too, but I am not sure
> what kinds of functionalities would be required in the end. Maybe I
> will try to check some other DBMSs too.
>
> > I still cannot fathom the application domain you're working in,
>
> I will give only the information which I gave already. The new items
> arrive, those will be kept in the DB for analysis (not for a very long
> time) and then the old items will be deleted. Items are not kept in
> the DB for a long time.
>
> Thank you for the comments and the information!

From your description of the constraints,
  * 900 columns per row,
  * searchable on any column,
  * minimum overhead,
  * data continually replaced

From the last one, I assume no updates between loads, only deletes and
inserts. IOW, I assume you
1. load a set of 900 column data
2. query the data and produce some analysis
3. delete all of that data in preparation for the next set

I would suggest not using a RDB at all. You may get better performance
and lower overhead writing a custom application that handles this
using flat data files and index files. Down side is you give up the
generic SQL interface. Although you could add that too since the logic
would be simple (only one table so no join logic needed, and no
optimizer since all columns are indexed).

It might take a little longer to build, but if your key constraints
are fast access and minimal overhead, then custom code may be the way
to go.

  HTH,
  ed