MySQL Performance Example Was: Seriously, WTF?

[Robbie Bow]

On Sat, May 10, 2008 at 7:58 AM, Jonathan Stowe <jns at gellyfish.com> wrote:
> 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.
>

I think that is mitigated if you ensure the myisam-recover server is
set appropriately.