Re: MySQL and PostgreSQL speed compare

Hi.

Just one note...

On Fri, Dec 29, 2000 at 07:01:21PM +0100, netletter(at)comder(dot)com wrote:
[...]
> >>> Sir, thanks for sharing this with us. However, unless you can explain
> >>> why queries inside of transactions run faster than queries outside of
> >>> transactions, I would be inclined to mistrust the test. I haven't
>
> I was suprised too. But the only difference is that I do a "BEGIN" before I
> start inserting/modifying/deleting and then when Im done I do a "COMMIT".
> Everything between those are exactly the same. Ive been told that MySQL does
> not support transactions (by default) so there the test is broken. And with
> PostgreSQL, well something inside PostgreSQL is broken (it cant be right
> that with transaction PostgreSQL is 10 times faster than without).

I don't know PostgreSQL, but I assume that BEGIN/COMMIT locks the
table (as it makes sense). Therefore it seems completely normal to me
that 1000 seperate INSERTs need more time, because they do 1000
seperate "transactions" and therefore seperate 1000 writes.

As transaction the writing behaviour is probably the same as if you
write LOCK/UNLOCK around the INSERTs and all writes will be issued at
one time, which usually is a lot faster (less disk writes, less checks
and so on).

It is a common optimization to use LOCK/UNLOCK to get faster mass
INSERTs. So the above behaviour is what I would expect.

On the other hand, you would have to use LOCK/UNLOCK on MySQL to
compare speed with transactions (given that reliability does not
count) or use BDB tables with BEGIN/COMMIT instead.

Bye,

Benjamin.