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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
