Meningkatkan performance MySql Database

Saya pernah baca beberapa artikel tentang bagaimana meningkatkan kinerja mysql. Karena terkadang sewaktu kita melakukan eksekusi pada database kita, terasa kurang yahud atau kurang mak wuss.. Ada beberapa tip bagaimana meningkatkan kinerja mysql.

Inilah tip2nya : (English Version)

When you design database

One of the most basic optimisation is to get your data (and indexes) to take as little space on the disk (and in memory) as possible. This can give huge improvements because disk reads are faster and normally less main memory will be used. Indexing also takes less resources if done on smaller columns.

  1. Use the most efficient (smallest) types possible. MySQL has many specialised types that save disk space and memory.
  2. Use the smaller integer types if possible to get smaller tables. For example, MEDIUMINT is often better than INT.
  3. Declare columns to be NOT NULL if possible. It makes everything faster and you save one bit per column. Note that if you really need NULL in your application you should definitely use it. Just avoid having it on all columns by default.
  4. Only create the indexes that you really need. Indexes are good for retrieval but bad when you need to store things fast. If you mostly access a table by searching on a combination of columns, make an index on them. The first index part should be the most used column. If you are always using many columns, you should use the column with more duplicates first to get better compression of the index.
  5. In some circumstances it can be beneficial to split into two a table that is scanned very often.

Explaining EXPLAIN (see mysql manual)

When you find that your queries take too long, the EXPLAIN command can give you some pointers about how to make the query run more efficiently. The EXPLAIN statement can be used either as a synonym for DESCRIBE or as a way to obtain information about how MySQL executes a SELECT statement:

  1. EXPLAIN tbl_name is synonymous with DESCRIBE tbl_name or SHOW COLUMNS FROM tbl_name. It describes the table structure.
  2. EXPLAIN SELECT something from tbl_name returns a row of information for each table used in the SELECT statement.

EXPLAIN SELECT * FROM table
WHERE non_indexed_field = 'value';

This is not a very good query. One of the first things to look at is the ‘type’ column. The ALL in this case shows us that MySQL is looking in all the rows for data. The other possible values for type (in order from best to worst) are – system, const, eq_ref, ref, range, and index.
Compare with this good example:

EXPLAIN SELECT * FROM table
WHERE indexed_field = 'value';

Consider the time of other users:

Please consider these actions when creating databases.

  1. Use INSERT DELAYED when you do not need to know when your data is written. This speeds things up because many records can be written with a single disk write.
  2. Use INSERT LOW_PRIORITY to give right to clients waiting data from a SELECT.

How to Search and Replace text?

UPDATE [table] SET
[field] = replace( [field], "search_for", "replace_with" );

Speed of INSERT queries

You can speed up insertions that is done over multiple statements by locking your tables:

mysql> LOCK TABLES aTable WRITE;
INSERT INTO aTable VALUES (1,23),(2,34),(4,33);
INSERT INTO aTable VALUES (8,26),(6,29);
UNLOCK TABLES;

The main speed difference is that the index buffer is flushed to disk only once, after all INSERT statements have completed. Normally there would be as many index buffer flushes as there are different INSERT statements.
As INSERT, UPDATE, and DELETE operations are very fast in MySQL, you will obtain better overall performance by adding locks around everything that does more than about 5 inserts or updates in a row. If you do very many inserts in a row, you could do a LOCK TABLES followed by an UNLOCK TABLES once in a while (about each 1000 rows) to allow other threads access to the table.

Selamat mencoba !

One Response to “Meningkatkan performance MySql Database”

  1. fuad hasan Says:

    Wow bagus ya tutorialnya……….


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: