Hi Jigal,
Jigal van Hemert schrieb:
> From: "Andreas Brandl"
>
>
>>ich stehe gerade vor dem Problem, dass ich eine große Datenbank (ca. 2
>>Mio. Datensätze) optimieren muss.
>
> Since this list is in English I'll answer you in English, so the others can
> join the fun!
Well, I didnt notice the list is in English :)
So, thank you for translating :)
> You have to optimize a large database (2 Million records).
>
>
>
> 'bid' is primary field. There are a few x-id fields which are queried from
> time tot time, a few varchars which are rarely queried and a few informative
> fields.
>
>
> How do you optimally define the indexes?
>
> Well, first of all I would consider the table type. For this type of
> database the MyISAM and InnoDB table types can be used.
> The differences in short are:
> - MyISAM is fast in small tables
> - MyISAM is fast when you have very little inserts/updates and many selects
> - InnoDB is faster when you have about the same number of inserts/updates
> and selects.
> - InnoDB supports transactions
> - MyISAM supports full text indexes
> More details can be found in the MySQL documentation, but these were the
> main differences.
>
> Your choice does not really influence the way you make indexes, but it
> may/will influence the overall performance.
>
> Warning: InnoDB seems very slow with small datasets, but in high concurrency
> situations (about equal amounts of reads and writes) MyISAM will get slower
> when the number of records increases, while InnoDB will be roughly constant
> in speed.
Ok, Im using myISAM because of much reading work, only little writing at
all...
[...]
>
> Oh, please read the manual on the MySQL site. It contains a couple of
> articles on query optimization, etc.
I promise to do!
>
> Regards, Jigal.
>
Thank you very much for the extensive answer. I'll try your proposals.
Regards, Andreas
