How to implement a large 2-d array ?

[siliconmike]

Hello, I'd like to implement a large 2-d array

2d array size: 5000 x 5000
each element: int 4 bytes
type: static, MYISAM
how is this done in MySQL? I'm a newbie, so details will help.

Mike

[Jeff North]

On 29 Jan 2005 21:45:02 -0800, in mailing.databas e.mysql "siliconmik e"
<si*********@ya hoo.com> wrote:

| Hello, I'd like to implement a large 2-d array
|
| 2d array size: 5000 x 5000
| each element: int 4 bytes
| type: static, MYISAM
| how is this done in MySQL? I'm a newbie, so details will help.

Maybe:

CREATE TABLE `tblArray` (
`fRow` int(11) NOT NULL default '0',
`fCol` int(11) NOT NULL default '0',
KEY `IDXrow` (`fRow`),
KEY `IDXcol` (`fCol`)
) TYPE=MyISAM;

Then use:
select * from tblArray where fRow=' + myRow + ' and fCol=' + myCol;
---------------------------------------------------------------
jn******@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

[Bill Karwin]

Jeff North wrote:

| 2d array size: 5000 x 5000
| each element: int 4 bytes

CREATE TABLE `tblArray` (
`fRow` int(11) NOT NULL default '0',
`fCol` int(11) NOT NULL default '0',
KEY `IDXrow` (`fRow`),
KEY `IDXcol` (`fCol`)
) TYPE=MyISAM;

One should add a field for the data at a given array element. And
presumably one would want a 2-d array per record of another table, so
one should add a foreign key referencing the parent table.

CREATE TABLE `tblArray` (
`fRow` int(11) NOT NULL default '0',
`fCol` int(11) NOT NULL default '0',
`elementData` integer,
`parentRef` integer not null references parentTable(pri maryKey)
KEY `IDXrow` (`fRow`),
KEY `IDXcol` (`fCol`)
) TYPE=MyISAM;

Then you could fetch the array as follows:

select fRow, fCol, elementData
from parentTable P inner join tblArray A on P.primaryKey = A.parentRef

You can fetch totals by column as follows:

select fCol, sum(elementData )
from parentTable P inner join tblArray A on P.primaryKey = A.parentRef
group by fCol;

See also chapter 23 of "SQL for Smarties" by Joe Celko. He talks about
storing and using array structures in SQL databases.

Regards,
Bill K.

[Wouter]

"Jeff North" <jn****@bigpond .net.au> wrote in message
news:db******** *************** *********@4ax.c om...

| 2d array size: 5000 x 5000

Maybe:

CREATE TABLE `tblArray` (
`fRow` int(11) NOT NULL default '0',
`fCol` int(11) NOT NULL default '0',
KEY `IDXrow` (`fRow`),
KEY `IDXcol` (`fCol`)
) TYPE=MyISAM;

Then use:
select * from tblArray where fRow=' + myRow + ' and fCol=' + myCol;

Maybe a Key for the fRow and fCol combined will speed up even more?

Wouter

[Jeff North]

On Mon, 31 Jan 2005 08:58:21 +0100, in mailing.databas e.mysql "Wouter"
<no*****@no.mai l.for.me> wrote:

| "Jeff North" <jn****@bigpond .net.au> wrote in message
| news:db******** *************** *********@4ax.c om...
| >>| 2d array size: 5000 x 5000
| > Maybe:
| >
| > CREATE TABLE `tblArray` (
| > `fRow` int(11) NOT NULL default '0',
| > `fCol` int(11) NOT NULL default '0',
| > KEY `IDXrow` (`fRow`),
| > KEY `IDXcol` (`fCol`)
| > ) TYPE=MyISAM;
| >
| > Then use:
| > select * from tblArray where fRow=' + myRow + ' and fCol=' + myCol;
|
| Maybe a Key for the fRow and fCol combined will speed up even more?

Wouter and Bill, I agree with both of your excellent suggests but the
OP didn't give many details to work on :-(
---------------------------------------------------------------
jn******@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------