Optimize MySQL

When MySQL uses indexes

  • Using >, >=, =, <, <=, IF NULL and BETWEEN on a key.
    • SELECT * FROM tablename WHERE keypart1=1 and keypart2 > 5;
    • SELECT * FROM tablename WHERE keypart1 IS NULL;
  • When you use a LIKE that doesn't start with a wildcard.
    • SELECT * FROM tablename WHERE keypart1 LIKE 'jani%'
  • Retrieving rows from other tables when performing joins.
    • SELECT * from t1,t2 where t1.col=t2.keypart
  • Find the MAX() or MIN() value for a specific index.
    • SELECT MIN(keypart2),MAX(keypart2) FROM tablename where keypart1=10
  • ORDER BY or GROUP BY on a prefix of a key.
    • SELECT * FROM foo ORDER BY keypart1,keypart2,keypart3
  • When all columns used in the query are part of one key.
    • SELECT keypart3 FROM tablename WHERE keypart1=1

When MySQL doesn't use an index

  • Indexes are NOT used if MySQL can calculate that it will probably be faster to scan the whole table.
  • For example if keypart1 is evenly distributed between 1 and 100, it's not good to use an index in the following query:
    • SELECT * FROM tablename where keypart1 > 1 and keypart1 < 90
  • If you are using HEAP tables and you don't search on all key parts with =
  • When you use ORDER BY on a HEAP table
  • If you are not using the first key part
    • SELECT * FROM tablename WHERE keypart2=1
  • If you are using LIKE that starts with a wildcard
    • SELECT * FROM tablename WHERE keypart1 LIKE '%jani%'
  • When you search on one index and do an ORDER BY on another
    • SELECT * from tablename WHERE keypart1 = # ORDER BY key2

Learn to use EXPLAIN

Use EXPLAIN on every query that you think is too slow!

mysql> explain select t3.DateOfAction, t1.TransactionID 	-> from t1 join t2 join t3 	-> where t2.ID = t1.TransactionID and t3.ID = t2.GroupID       	-> order by t3.DateOfAction, t1.TransactionID;   +-------+--------+---------------+---------+---------+------------------+------+---------------------------------+   | table | type   | possiblekeys | key     | keylen | ref              | rows | Extra                           |   +-------+--------+---------------+---------+---------+------------------+------+---------------------------------+   | t1    | ALL    | NULL          | NULL    |    NULL | NULL             |   11 | Using temporary; Using filesort |   | t2    | ref    | ID            | ID      |       4 | t1.TransactionID |   13 |                                 |   | t3    | eqref | PRIMARY       | PRIMARY |       4 | t2.GroupID       |    1 |                                 |   +-------+--------+---------------+---------+---------+------------------+------+---------------------------------+  

Types ALL and range signal a potential problem.


Learn to use SHOW PROCESSLIST

Use SHOW processlist to find out what is going on:

+----+-------+-----------+----+---------+------+--------------+-------------------------------------+   | Id | User  | Host      | db | Command | Time | State        | Info                                |   +----+-------+-----------+----+---------+------+--------------+-------------------------------------+   | 6  | monty | localhost | bp | Query   | 15   | Sending data | select * from station,station as s1 |   | 8  | monty | localhost |    | Query   | 0    |              | show processlist                    |   +----+-------+-----------+----+---------+------+--------------+-------------------------------------+  

Use KILL in mysql or mysqladmin to kill off runaway threads.


How to find out how MySQL solves a query

Run the following commands and try to understand the output:

  • SHOW VARIABLES;
  • SHOW COLUMNS FROM ...\G
  • EXPLAIN SELECT ...\G
  • FLUSH STATUS;
  • SELECT ...;
  • SHOW STATUS;

MySQL is extremely good

  • For logging.
  • When you do many connects; connect is very fast.
  • Where you use SELECT and INSERT at the same time.
  • When you don't combine updates with selects that take a long time.
  • When most selects/updates are using unique keys.
  • When you use many tables without long conflicting locks.
  • When you have big tables (MySQL uses a very compact table format).

Things to avoid with MySQL

  • Updates to a table or INSERT on a table with deleted rows, combined with SELECTS that take a long time.
  • HAVING on things you can have in a WHERE clause.
  • JOINS without using keys or keys which are not unique enough.
  • JOINS on columns that have different column types.
  • Using HEAP tables when not using a full key match with =
  • Forgetting a WHERE clause with UPDATE or DELETE in the MySQL monitor. If you tend to do this, use the --i-am-a-dummy option to the mysq client.

Different locks in MySQL

  • Internal table locks.
  • LOCK TABLES (Works on all table types)
  • GETLOCK()/RELEASELOCK()
  • Page locks (for BDB tables)
  • ALTER TABLE also does a table lock on BDB tables.
  • LOCK TABLES gives you multiple readers on a table or one writer.
  • Normally a WRITE lock has higher priority than a READ lock to avoid starving the writers. For writers that are not important one can use the LOWPRIORITY keyword to let the lock handler prefer readers.
      UPDATE LOWPRIORITY SET value=10 WHERE id=10;  

Tricks to give MySQL more information to solve things better

Note that you can always comment out a MySQL feature to make the query portable:

SELECT /*! SQLBUFFERRESULTS */ ...  
  • SELECT SQLBUFFERRESULTS ...
    Will force MySQL to make a temporary result set. As soon as the temporary set is done, all locks on the tables are released. This can help when you get a problem with table locks or when it takes a long time to transfer the result to the client.
  • SELECT SQLSMALLRESULT ... GROUP BY ...
    To tell the optimizer that the result set will only contain a few rows.
  • SELECT SQLBIGRESULT ... GROUP BY ...
    To tell the optimizer that the result set will contain many rows.
  • SELECT STRAIGHTJOIN ...
    Forces the optimizer to join the tables in the order in which they are listed in the FROM clause.
  • SELECT ... FROM tablename [USE INDEX (indexlist) | IGNORE INDEX (indexlist)] tablename2
    Forces MySQL to use/ignore the listed indexes.

Example of doing transactions

  • How to do a transaction with MyISAM tables:
    mysql> LOCK TABLES trans READ, customer WRITE;  mysql> select sum(value) from trans where customerid=someid;  mysql> update customer set totalvalue=sumfrompreviousstatement             where customerid=someid;  mysql> UNLOCK TABLES;  
  • How to do a transaction with Berkeley DB tables:
    mysql> BEGIN WORK;   mysql> select sum(value) from trans where customerid=someid;  mysql> update customer set totalvalue=sumfrompreviousstatement             where customerid=someid;  mysql> COMMIT;  
  • Note that you can often avoid transactions altogether by doing:
    UPDATE customer SET value=value+newvalue WHERE customerid=someid;  

Example of using REPLACE

REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a unique index, the old record is deleted before the new record is inserted. Instead of using

  SELECT 1 FROM t1 WHERE key=#     	IF found-row       		LOCK TABLES t1       		DELETE FROM t1 WHERE key1=#       		INSERT INTO t1 VALUES (...)       		UNLOCK TABLES t1;     	ENDIF  

Do

  REPLACE INTO t1 VALUES (...)      

General tips

    • Use short primary keys. Use numbers, not strings, when joining tables.
    • When using multi-part keys, the first part should be the most-used key.
    • When in doubt, use columns with more duplicates first to get better key compression.
    • If you run the client and MySQL server on the same machine, use sockets instead of TCP/IP when connecting to MySQL (this can give you up to a 7.5 % improvement). You can do this by specifying no hostname or localhost when connecting to the MySQL server.
    • Use --skip-locking (default on some OSes) if possible. This will turn off external locking and will give better performance.
    • Use application-level hashed values instead of using long keys:
SELECT * FROM tablename WHERE hash=MD5(concat(col1,col2)) AND     col1='constant' AND col2='constant'  
  • Store BLOB's that you need to access as files in files. Store only the file name in the database.
  • It is faster to remove all rows than to remove a large part of the rows.
  • If SQL is not fast enough, take a look at the lower level interfaces to access the data.

Benefits of using MySQL 3.23

  • MyISAM ; Portable BIG table format
  • HEAP ; In memory tables
  • Berkeley DB ; Transactional tables from Sleepycat.
  • A lot of raised limits
  • Dynamic character sets
  • More STATUS variables.
  • CHECK and REPAIR table.
  • Faster GROUP BY and DISTINCT
  • LEFT JOIN ... IF NULL optimization.
  • CREATE TABLE ... SELECT
  • CREATE TEMPORARY tablename (...)
  • Automatic conversion of temporary HEAP to MyISAM tables
  • Replication
  • mysqlhotcopy script.

Important features that we are actively working on

  • Improving transactions
  • Fail safe replication
  • Text searching
  • Delete with many tables (Updates with many tables will be done after this.)
  • Better key cache
  • Atomic RENAME (RENAME TABLE foo as fooold, foo_new as foo)
  • A query cache
  • MERGE TABLES
  • A better GUI client