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.
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 | |
+-----+-----+-------------+-------+-------+-----+----+-----+
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.
UPDATE [table] SET [field] = replace( [field], "search_for", "replace_with" );
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.