Optimizing db performance

A couple of weeks ago my colleague Daniel asked me tips and info on optimizing db.
Afficher en écriture latine

With the documentation I have, I found a certain number of techniques, some available on opensource DBMS and  other only on proprietary DBMS.

• Indexing normaly when you query a table all the table data will be read (full table scan). Indexing data is like indexes in a book. So primary key is a special kind of index, it's the unique identifier for a row on the db. How to build indexes? the answer is not easy, but we must always remember that creating a db is not a project but an ongoing process.
  If for example I want to get all the sales ordered by date I will create an index on date_sales field with descending sort order. In this way retrieving last week's sales won't be slow.
• Bitmap indexing this is a sepcial form of index (unfortunatly  not available on MySql) normaly used in columns with a low number of unique values gender, color, ...). The idea is to create for the distinct number of the values in the column, a bit projection is created. This enable faster access to the indexed values.
• Partitioning introduced in MySql 5.1 it's the capibility  to slice a table into multiple physical pieces. For example a large table containing sales data can be partioned by year so that queries for a given year will be performed only on the portion of the data for the requested year.
• ...

 This is only a starting point, in future I will investigate Change Data Capture

techniques, use of products such as Kettle, Mondrian, ...

Good eveningRinaldo