470,591 Members | 2,170 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,591 developers. It's quick & easy.

Max columns limitation

Hi,

I'm using MySQL on Windows and I hit a limit somewhere
between 1300 and 1500 columns in a table. (There is
a good reason why I need that many columns - so please
bear with me.)

Is there any way to allow a table to have perhaps 2,000 to 5,000
columns without going to blob fields where I have to manage
the columns.

Relational joins over multiple tables won't do the trick since
I would need a view this wide anyway.

Is there any open source database that can handle this
monster?

John

Jul 20 '05 #1
8 7480

"John Small" <js****@atlantech.net> wrote in message
news:VS******************@fe39.usenetserver.com...
Hi,

I'm using MySQL on Windows and I hit a limit somewhere
between 1300 and 1500 columns in a table. (There is
a good reason why I need that many columns - so please
bear with me.)

Is there any way to allow a table to have perhaps 2,000 to 5,000
columns without going to blob fields where I have to manage
the columns.

Relational joins over multiple tables won't do the trick since
I would need a view this wide anyway.

Is there any open source database that can handle this
monster?

John


I am bearing with you. I know of no product that allows this many columns.
Please explain why you have so many columns. Are they attributes? If so,
what kind of entity needs 1500 attributes? Or do you have so many relations
that foreign keys are migrated? Are you doing a time series in a table? I'm
just curious. Best of luck....

Rich R.
Jul 20 '05 #2
John Small wrote:
Is there any way to allow a table to have perhaps 2,000 to 5,000
columns without going to blob fields where I have to manage
the columns.


The fact that you can't give a firm value for the number of columns
suggests to me that it's liable to grow, so even if you find an RDBMS
that stores 5,000 (or 10,000) columns, it won't be enough for long.

You need to create a second table to store all those values, referencing
the primary key of the first table. Join these two tables together and
fetch all the matching values, and then if you need to reassemble it
into an array in your application, do it in your application code. Not
every task can be done in a single SQL query.

If you're trying to deliver the dataset as a single row to some report
writer or other middleware product, I would look for another way to
represent the information. I just can't believe that creating a
5,000-column table is the best solution for anything!

Regards,
Bill K.
Jul 20 '05 #3

"Bill Karwin" <bi**@karwin.com> wrote in message
news:cp*********@enews4.newsguy.com...
John Small wrote:
Is there any way to allow a table to have perhaps 2,000 to 5,000
columns without going to blob fields where I have to manage
the columns.


The fact that you can't give a firm value for the number of columns
suggests to me that it's liable to grow, so even if you find an RDBMS
that stores 5,000 (or 10,000) columns, it won't be enough for long.

You need to create a second table to store all those values, referencing
the primary key of the first table. Join these two tables together and
fetch all the matching values, and then if you need to reassemble it
into an array in your application, do it in your application code. Not
every task can be done in a single SQL query.

If you're trying to deliver the dataset as a single row to some report
writer or other middleware product, I would look for another way to
represent the information. I just can't believe that creating a
5,000-column table is the best solution for anything!

Regards,
Bill K.


Yes, Bill, you are correct, If the columns grow, then something is wrong
with the design. And the second table would do it. But I would still love to
know the problem he is trying to solve.

Rich
Jul 20 '05 #4
Basically I'm storing heterogenius, hierarchical data and ultimately
it has to be flattened. I'm flattening it outside the database now.
What I really need is a deductive/associative database or a multidimensional
database but the customer wants it on the cheap. Sorry I can't give
you more details.

Thanks for your help!

"Rich R" <rr***@cshore.com> wrote in message
news:hX*******************@newssvr33.news.prodigy. com...

"Bill Karwin" <bi**@karwin.com> wrote in message
news:cp*********@enews4.newsguy.com...
John Small wrote:
> Is there any way to allow a table to have perhaps 2,000 to 5,000
> columns without going to blob fields where I have to manage
> the columns.


The fact that you can't give a firm value for the number of columns
suggests to me that it's liable to grow, so even if you find an RDBMS
that stores 5,000 (or 10,000) columns, it won't be enough for long.

You need to create a second table to store all those values, referencing
the primary key of the first table. Join these two tables together and
fetch all the matching values, and then if you need to reassemble it
into an array in your application, do it in your application code. Not
every task can be done in a single SQL query.

If you're trying to deliver the dataset as a single row to some report
writer or other middleware product, I would look for another way to
represent the information. I just can't believe that creating a
5,000-column table is the best solution for anything!

Regards,
Bill K.


Yes, Bill, you are correct, If the columns grow, then something is wrong
with the design. And the second table would do it. But I would still love
to
know the problem he is trying to solve.

Rich


Jul 20 '05 #5
John Small wrote:
Basically I'm storing heterogenius, hierarchical data and ultimately
it has to be flattened. I'm flattening it outside the database now.
What I really need is a deductive/associative database or a multidimensional
database but the customer wants it on the cheap. Sorry I can't give
you more details.


Hierarchical data?

So perhaps something like:

id, parent_id, data

Example data:
title1
|- title1A
|- title1B
| |- title1B1
| |- title1B2
|- title1C
title2

Data in database:
-----------------------------
| id | parent_id | data |
|---------------------------|
| 1 | 0 | title1 |
| 2 | 0 | title2 |
| 3 | 1 | title1A |
| 4 | 1 | title1B |
| 5 | 1 | title1C |
| 6 | 4 | title1B1 |
| 7 | 4 | title1B2 |
-----------------------------

The above example is good for some cases and bad for others. Of course
one solution could be also to save the whole path to the column, for
example in text-column, using indexes separated by commas.

-----------------------------
| id | parent_id | data |
|---------------------------|
| 1 | 0 | title1 |
| 2 | 0 | title2 |
| 3 | 1 | title1A |
| 4 | 1 | title1B |
| 5 | 1 | title1C |
| 6 | 1,4 | title1B1 |
| 7 | 1,4 | title1B2 |
-----------------------------

But again, this has it flaws. For example updating the hierarchy needs
much more work than the first example needed. But then again, it is much
easier to find all the parents for certain leaf.

The example given in here is simplified example. This can be used as it
is, but "proper" way would propably be to add another table, where you
save all the parents and their order for certain child.

So something like:
leaf_id | parent_id | order
---------------------------
3 | 1 | 1
4 | 1 | 1
5 | 1 | 1
6 | 1 | 1
6 | 4 | 2
7 | 1 | 1
7 | 4 | 2

The above contains the same information as previous, but this time the
data is separated to rows, as it should be in databases. From this table
you can easily search all parents for certain leaf,

select parent_id from table where leaf_id=6;

but you can also search all children and grant children etc. for certain
parent.
select leaf_id from table where parent_id=1;

I bulieve there are other solutions also, it greatly depends on the
details of the problem which is best to use.
Jul 20 '05 #6

"John Small" <js****@atlantech.net> wrote in message
news:dX***********@fe61.usenetserver.com...
Basically I'm storing heterogenius, hierarchical data and ultimately
it has to be flattened. I'm flattening it outside the database now.
What I really need is a deductive/associative database or a multidimensional database but the customer wants it on the cheap. Sorry I can't give
you more details.

Huh? Makes no sense to me. "heterogenius, hierarchical data and
"deductive/associative database or a multidimensional database". Give me an information artifact->a report with headings. Then I

may understand what you are talking about.

Rich R.

Rich R.
Jul 20 '05 #7
John Small wrote:
Basically I'm storing heterogenius, hierarchical data and ultimately
it has to be flattened. I'm flattening it outside the database now.


I recommend you continue to flatten the data outside the database.

You might waste a lot of time searching for an RDBMS that *might* handle
thousands of columns, but you'll certainly waste a lot of time trying to
get it to work reliably. It won't be "on the cheap" for your client
either way.

Just my opinion,
Bill K.
Jul 20 '05 #8
guzguz
1
wow .... 5,000 - 10,000 columns!!!!
are you from another world?
Apr 22 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Seong-Kook Shin | last post: by
3 posts views Thread by SDRoy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.