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!
You have to optimize a large database (2 Million records). > Feld 'bid' als primary > Ein paar x-id Felder, nach denen ab und an gesucht wird > Einige varchars nach denen selten gesucht wird > Und ein paar andere sachen, die aber nur informationellen Charakter haben '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. > Wie lege ich nun die Indizes optimal? 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. > Macht es Sinn, Indizes über Felder zu legen, die oft in WHERE-Klauseln > vorkommen? In general you analyze the queries and list all the fields that are used in JOINs, WHERE clauses, GROUP BY and ORDER BY, etc.; actually everything except the fields listed in the SELECT part. Then you try to define the smallest number of indexes which use all these fields. You can define indexes which use more than one field. Not all the fields in an index must be used in a query, as long as the leftmost fields are used: For example you have an combined index on field c1+c2+c3+c5. You can use this index for queries using c1,c2 and c3 or c1 and c2, but not for a query that uses c3 and c5. > Aber ein Index über ein varchar(250)-Feld ist auch nicht gerade > sinnvoll, richtig? No, but you make an index on for example the first 20 characters of that field. It will narrow down the number of records that have to be fully searched, so it will make the query faster. You can only use one index per table in the query. Use EXPLAIN SELECT .... to see the use of indexes, etc. Experience showed me that the number of records that is show for each step in the output will greatly determine the speed of the query. Sometimes it's faster to move certain conditions from the WHERE clause to the JOIN parts; conditions in the JOIN will determine which records are combined in the JOIN, whereas the conditions in the WHERE clause will limit the resultset. In some cases the netto result would be the same whether the condition is in the JOIN or in the WHERE, but limiting the number of records used in the JOIN may speed up your query. Use EXPLAIN to check. > Wie kann ich die DB optimieren? You can normalize the tables. If you have fields with only a limited number of string values, you can replace them with an integer id number and list the strings in a different table. Oh, please read the manual on the MySQL site. It contains a couple of articles on query optimization, etc. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
