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]

Re: How to implement a large 2-d array ?

On 29 Jan 2005 21:45:02 -0800, in mailing.databas e.mysql "siliconmik e"
<siliconmike@ya hoo.com> wrote:
[color=blue]
>| 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.[/color]

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;
---------------------------------------------------------------
jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

[Bill Karwin]

Re: How to implement a large 2-d array ?

Jeff North wrote:[color=blue][color=green]
>>| 2d array size: 5000 x 5000
>>| each element: int 4 bytes[/color]
>
> 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;[/color]

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]

Re: How to implement a large 2-d array ?

"Jeff North" <jnorth@bigpond .net.au> wrote in message
news:dbapv0tk7p o949m05sn230cmf 6m1f9au5v@4ax.c om...[color=blue][color=green]
>>| 2d array size: 5000 x 5000[/color]
> 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;[/color]

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

Wouter

[Jeff North]

Re: How to implement a large 2-d array ?

On Mon, 31 Jan 2005 08:58:21 +0100, in mailing.databas e.mysql "Wouter"
<no.spam@no.mai l.for.me> wrote:
[color=blue]
>| "Jeff North" <jnorth@bigpond .net.au> wrote in message
>| news:dbapv0tk7p o949m05sn230cmf 6m1f9au5v@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?[/color]

Wouter and Bill, I agree with both of your excellent suggests but the
OP didn't give many details to work on :-(


---------------------------------------------------------------
jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------