Tool Development

Some tips and suggestions to speed-up your database performance and allow other users to work at the same time.

While designing your database ... (see data size) Go to top of the page

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.


Explaining EXPLAIN (see mysql open in new windowmanual) Go to top of the page

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:

- EXPLAIN tbl_name is synonymous with DESCRIBE tbl_name or SHOW COLUMNS FROM tbl_name. It describes the table structure.

- 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'; +-----+----+-------------+----+-------+----+----+----------+ |table|type|possible_keys|key |key_len|ref |rows|Extra | +-----+----+-------------+----+-------+----+----+----------+ |table|ALL |NULL |NULL|NULL |NULL|4829|where used| +-----+----+-------------+----+-------+----+----+----------+ 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'; +-----+-----+-------------+-------+-------+-----+----+-----+ |table|type |possible_keys|key |key_len|ref |rows|Extra| +-----+-----+-------------+-------+-------+-----+----+-----+ |table|const|PRIMARY |PRIMARY|3 |const|1 | | +-----+-----+-------------+-------+-------+-----+----+-----+

Consider the time of other users: Go to top of the page

Please consider these actions when creating databases.

- 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.

- Use INSERT LOW_PRIORITY to give right to clients waiting data from a SELECT.


How to Search and Replace text? Go to top of the page

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

Speed of INSERT queries Go to top of the page

You can speed up insertions that is done over multiple statements by locking your tables: mysql> LOCK TABLES aTable WRITE; mysql> INSERT INTO aTable VALUES (1,23),(2,34),(4,33); mysql> INSERT INTO aTable VALUES (8,26),(6,29); mysql> 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.


by Jaime Prilusky, 2005. For more details, comments and suggestions, drop an email to jaime.prilusky@weizmann.ac.il