For starters,
Just testing last week, I noticed quite a difference between
- `longitude` float(10,6) NOT NULL DEFAULT '0',
- `latitude` float(10,6) NOT NULL DEFAULT '0',

and the current
- `latitude` double NOT NULL DEFAULT '0',
- `longitude` double NOT NULL DEFAULT '0',

Doubles are twice as large and extremely wasteful. Also, there really
isn't any need to know within a millimeter, the latitude/longitude. 6
decimal places will more than suffice (XXXX.XXXXXX). Combine this with
indexing and we might be getting somewhere.




On Mon, Jul 16, 2012 at 6:25 PM, Pablo A. Destéfanis
<pdestefanis@...> wrote:

I agree with Emmanuel, and let me add:



·         While PostGIS is excellent, quite a lot can be done with MySQL’s
spatial extensions. I don’t swear over it, but the complexity of porting the
app needs to be weighed against the limitations (also known as the “how many
ppl would need it”).

·         If you have a big site, you can always offload your data to your
DB of choice. To me that is part of the “a factory installation won’t cut
it”

·         Not sure how the NoSQL option will.

·         I do not have info on slow queries with me, but I’m sure code
could be tightened. While I can’t point to examples, I do recall this from
looking at several query-logs in MySQL.



Last, if anyone is interested in testing this, I would love to stay in
touch.



Cheers,


Pablo



From: Emmanuel Kala [mailto:emkala@...]
Sent: Monday, July 16, 2012 14:50
To: developers@...
Subject: Re: [ushahidi developers] PHP/MySQL



Some notes:

Scale is a function of design
MySQL scales quite well...when you know what you're doing but it's not very
inspiring for geo-spatial work. PostgreSQL (+PostGIS) has clearly become the
preferred choice for such.
For high traffic deployments, a factory installation won't cut it - some
load balancing, caching, DB tuning etc work has to be done  in addition to
exorcising ghosts that are always inherent in the machine
The NoSQL options, e.g. Redis, are pretty good for caching (Reddit and big
xxx sites)
Profiling code has provided great insights on what sections of the
application are yielding beastly load times

Thanks.



On Mon, Jul 16, 2012 at 10:59 PM, João Peixoto <joao.mpfp@...> wrote:

Hello all,



First post here, not really an Ushahidi developer yet, for the moment the
NGO I work with has a couple projects that use it and since we love it,
we're starting customization to meet our needs (which differ a bit from the
typical Crwodmap sites - it's a biking support site).



On Mon, Jul 16, 2012 at 4:51 PM, David Kobia <david@...> wrote:

[...]



An option might be to ensure that database interaction is abstracted
enough that it doesn't matter what the underlying database is... and
this is something that might be worth looking into.



I'd love to see some DB abstraction on Ushahidi. If possible, have a "quick
and dirty" approach using mysql that allows a quick deployment, but as a
site scales up and requires further optimizations, allow better back-ends
for efficiency.



My 2 cents!



JP





--

Kind Regards,

Emmanuel Kala



Skype: emmanuel.kala



Judgement comes from experience, experience comes from poor judgement

~~~~~~~~~~~~~~~~~~~~~~~~~~
List Archive: http://list.ushahidi.com/

Would you like to receive list mail batched in a daily digest instead? Send a message to:
developers-digest-subscribe@...

To remove your address from the list, just send a message to
the address in the "List-Unsubscribe" header of any list
message. If you haven't changed addresses since subscribing,
you can also send a message to:
developers-unsubscribe@...

For addition or removal of addresses, we'll send a confirmation
message to that address. When you receive it, simply reply to it
to complete the transaction.

If you need to get in touch with the human owner of this list,
please send a message to:
developers-owner@...