468,278 Members | 1,578 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

How to implement a large 2-d array ?

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

Jul 23 '05 #1
4 1427
On 29 Jan 2005 21:45:02 -0800, in mailing.database.mysql "siliconmike"
<si*********@yahoo.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******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Jul 23 '05 #2
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(primaryKey)
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.
Jul 23 '05 #3
"Jeff North" <jn****@bigpond.net.au> wrote in message
news:db********************************@4ax.com...
| 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
Jul 23 '05 #4
On Mon, 31 Jan 2005 08:58:21 +0100, in mailing.database.mysql "Wouter"
<no*****@no.mail.for.me> wrote:
| "Jeff North" <jn****@bigpond.net.au> wrote in message
| news:db********************************@4ax.com...
| >>| 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******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Developwebsites | last post: by
4 posts views Thread by fivelitermustang | last post: by
13 posts views Thread by Sherif ElMetainy | last post: by
3 posts views Thread by meltedown | last post: by
7 posts views Thread by ultr | last post: by
10 posts views Thread by Peter Duniho | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.