Tommy Pham wrote:
> On Wed, Mar 31, 2010 at 11:01 AM, Nathan Rixham <> wrote:
>> Tommy Pham wrote:
>>> On Wed, Mar 31, 2010 at 4:06 AM, Nathan Rixham <> wrote:
>>>> Tommy Pham wrote:
>>>>> As for spatial data types, I've never find much use for non scientific
>>>>> related.  (example) If using point as a PK, if MySQL stores it the
>>>>> same way as PostgreSQL which is 16 bytes, how is that any different -
>>>>> performance wise - than using UUID and storing it as binary(16) for
>>>>> MySQL or uniqueidentifier (16 bytes) for PostgreSQL?
>>>> it's all about the indexing (R-Tree)
>>> I can see where the performance would be between B-Tree vs R-Tree for
>>> the same field size but I've yet to see real life application of it.
>>> Case in point, if using point for GPS data coordinates, then wouldn't
>>> it still be a lot better to use Lon (float), Lat (float) which is 2
>>> fields of 4 bytes each vs 1 field of 16 bytes?  The index would still
>>> be faster (8 bytes less) in B-Tree right?  Not to mention smaller row
>>> & DB size.
>> wish I still had an application to point you at; and I can't assert in
>> any other way just how much faster it is; especially over large datasets
>> - regardless of the amount of rows the style of index doesn't slow
>> (spatial) queries down.
> If I see the PoC w/o having to see the actual setup, then I would
> probably understand your point better.
>> if index byte size is of importance then negate spatial indexes (which
>> are rather a lot bigger).
> Since the spatial indexes are bigger, wouldn't that work against it
> due disk access having to read more?

depends on how seriously you take a solution tbh; if you just wanna run
a huge dataset on a sata drive w/ 2gb ram then your screwed whatever
approach (unless you turn the dataset in to many fine grained resources,
heavily cached and only accessed on a need to have basis) but even then
there are many limitations.

one approach is to throw more ram at a situation; where you are
definitely better loading a huge dataset in to one box with massive
amounts of ram than splitting it over multiple boxes. [1]

if you're really serious then you want to be on a box with a tonne of
ram and pci express solid state storage devices which give you
throughput of 700MB/s and higher; prices aren't that bad comparatively
and one machine w/ a $800 card added works much better than 2-3 servers
(although you still need to cover replication and high availability).

wandering off course a bit maybe; but these are situations we need to

I don't know how big you think big is; but realistically aws ec2
instances coupled with various "big data" / nosql - non rdbms approaches
are the way to go (see many many many recent discussions on the subject
around the net)

>> regardless though, it was just an idea I was throwing at you, not
>> suggesting it's the best approach, but worth consideration depending on
>> your priorities.
> Thanks for your idea.  I'd appreciate it very much.  I'm told that I
> over analyze things at times ... lol ... so please don't take it the
> wrong way :)

ty for clarifying, and deal - I'm the same :)

>>  As for calculating the distance between 2 'points', it
>>> would be simpler for querying purposes to use 'point'.  ATM, I don't
>>> have need to do any such calculations.  If I do use 'point' for PK,
>>> I'd run into problems with scaling and migration later.
>> the points don't matter tbh; or should i say specifying an x and a y
>> doesn't matter, because you can simply get by a single id, or get a
>> range of ids very quickly; the other value comes in to play when you
>> want temporal queries.
>> again though, i reiterate only useful if you want speed and don't care
>> about index bytesize - and certainly not forcing it down your neck.
>> re scaling.. unsure theoretically you have 15 digit precision which is a
>> mysql DOUBLE/REAL, and that's for each value in the pair, so an almost
>> incomprehensible number of rows are possible before a collision.
>> all in all: there are many good reasons why I've only used this on
>> occassion (specifically when dealing with 1 million+ rows in an RDBMS
>> table); and many more good reasons why i stick to non-rdbms databases
>> and use http uri's + eav model data only nowadays.
>> The latter i would advocate, the former not so unless you are stuck w/
>> mysql postgres - in which case you can't get faster. [1]
>> [1] get some numbers to prove when i have time.
>> Nathan
> Using HTTP URI's + EAV model is something I'll definitely look into.

good good; i can assure you it's the future; all the major corps and
governments are moving to this (age-old) model under the banner of
linked data, odata, and partially gdata - and meanwhile the development
community is slowly getting there with focus moving to kv databases
which are just one step away from the full on eav / triple approach.

Many regards,


PHP General Mailing List (
To unsubscribe, visit:

Reply via email to