MySQL Performance Example Was: Seriously, WTF?

[Jonathan Stowe]

On Fri, 2008-05-09 at 20:41 +0100, Robbie Bow wrote:
> On Fri, May 9, 2008 at 4:21 PM, Abigail <abigail at abigail.be> wrote:
> >>
> >> Go on then, tell me the easier, better way to hold these 100 million
> >> records and perform the kind of searches in question. Or even the
> >> transactions one should be "bothered" with in this scenario.
> >
> > Those 100 million records have to come from somewhere, did the rows
> > come with your version of MySQL? All MySQLs I installed just came with
> > empty tables.
> >
> > I agree, there are cases where you don't need a transaction if you are
> > searching. But I don't want to go from 0 rows to even 1 row without
> > transactions, let alone to 100 million.
> >
> 
> What do we gain from transactions when the only task is to insert data
> e.g. we go from
> 
> insert
> 
> to:
> 
> begin
> insert
> commit

My understanding is that without transactions in the database engine the
first is still afflicted by the fact that the update of the index is not
atomic to the insert, your insert might succeed and the index update
might fail - if subsequently the query optimizer chooses to use the
corrupt index to perform a query then your row has disappeared. 

/J\



-- 
My guitar kills bloggers