To: "Amit V Shah" <ashah@tagaudit.com>,
	"'pgsql-performance@postgresql.org'" <pgsql-performance@postgresql.org>
Subject: Re: Need help to decide Mysql vs Postgres
References: <0C072E7CC947D511AC9600A0CC7341200256CF03@xeon400.tagaudit.com>
Message-ID: <op.sratfwh2th1vuj@localhost>
Date: Tue, 24 May 2005 23:17:46 +0200
From: PFC <lists@boutiquenumerique.com>
Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?=
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
In-Reply-To: <0C072E7CC947D511AC9600A0CC7341200256CF03@xeon400.tagaudit.com>
User-Agent: Opera M2(BETA2)/8.0 (Linux, build 987)


	It's common knowledge, it seems, that MySQL without transactions will be  
a lot faster than Postgres on Inserts. And on Updates too, that is, unless  
you have more than a few concurrent concurrent connections, at which point  
the MySQL full table lock will just kill everything. And you don't have  
transactions, of course, and if something goes wrong, bye bye data, or  
funky stuff happens, like half-commited transactions if a constraint is  
violated in an INSERT SELECT, or you get 0 January 0000 or 31 February,  
etc.
	I heard it said that MySQL with transactions (InnoDB) is slower than  
postgres. I'd believe it... and you still get 00-00-0000 as a date for  
free.
	But from your use case postgres doesn't sound like a problem, yours  
sounds like a few big batched COPY's which are really really fast.

	And about SELECTs, this is really from an experience I had a few months  
ago, from a e-commerce site... well, to put it nicely, MySQL's planner  
don't know shit when it comes to doing anything a bit complicated. I had  
this query to show the "also purchased" products on a page, and also a few  
other queries, best buys in this category, related products, etc...,  
nothing very complicated really, at worst they were 4-table joins... and  
with 50K products MySQL planned it horrendously and it took half a second  
! Seq scans every times... I had to split the query in two, one to get the  
product id's, another one to get the products.
	I took the sql, put it in postgres with the usual changes (typenames,  
etc...) but same indexes, same data... the query took half a millisecond.  
Well... what can I say ?

	Also when you sit in front of the psql or mysql command line, it's an  
entirely different experience. One is a pleasure to work with... the other  
one is just a pain.