Large PHP Array Database Management

[drath]

I'm working on a fairly ambitious project and have a HUGE set of array values to store, edit, and call from the database. This is a general question in regards to the best way to do this. To give you an example of the size:

 

$tile[-33][423] = "green"
$tile[-99][-300] = "green"
$tile[1000][30] = "brown"
$tile[9][-1] = "red"
$tile[10939][-2] = "brown"

and so on...

 

There will possible be millions of variable array combinations here.

 

I was first thinking about serializing the array to store in a single field on the database. But the problem comes in when I want to edit a single tile variable, I would have to grab all the data (I think?), change one thing, put it all back - that would be pretty resource intensive, especially when I would need to make these edit calls 10 times a minute for example.

 

So that leaves me to the only other option I can think of, which is doing a column and row approach:

 

x

y

tile

100

5000

red

-211

7120

green

455

2

blue

and so on...

 

But then I thought, what is the performance going to be like if go to import 5000 variables into the database in one fell swoop? Or what about when I need to grab the data from 5000 of them to output? Maybe it won't be so bad? The SELECT, INSERT functions for these 5000 variables won't be happening near as much as the single UPDATE calls, but they could still be fairly frequent - perhaps once every 10 minutes for the INSERT, once every 2 minutes for the SELECT. Hopefully that made some sense.

 

Also, am I going to be running into any PHP/MySQL limitations here on int values, or array limits?

[fenway]

5000 isn't that many -- the SELECT on such small data should be fairly quick -- of course, how are you determining which 5000 to choose?