When MySQL uses indexes
BETWEENon a key.
SELECT * FROM tablename WHERE keypart1=1 AND key_part2 > 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.key_part
- Find the MAX() or MIN() value for a specific index.
SELECT MIN(keypart2),MAX(keypart2) FROM tablename where keypart1=10
GROUP BYon a prefix of a key.
SELECT * FROM foo ORDER BY keypart1,keypart2,key_part3
- When all columns used in the query are part of one key.
SELECT keypart3 FROM tablename WHERE key_part1=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
key_part1is 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 key_part1 < 90
- If you are using HEAP tables and you don't search on all key parts with
- When you use
ORDER BYon a
- 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
EXPLAIN on every query that you think is too slow!
EXPLAIN SELECT t3.date_of_action, t1.transaction_id FROM t1 JOIN t2 JOIN t3 WHERE t2.id = t1.transaction_id AND t3.id = t2.group_id ORDER BY t3.date_of_action, t1.transaction_id;
|t1||ALL||NULL||NULL||NULL||NULL||11||Using temporary; Using filesort|
range signal a potential problem.
Learn to use SHOW PROCESSLIST
SHOW processlist to find out what is going on:
|6||monty||localhost||bp||Query||15||Sending data||SELECT * FROM station,station AS s1|
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 COLUMNS FROM ...\G
EXPLAIN SELECT ...\G
MySQL is extremely good
- For logging.
- When you do many connects; Connect is very fast.
- Where you use
INSERTat 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
INSERTon a table with deleted rows, combined with SELECTS that take a long time.
HAVINGon things you can have in a WHERE clause.
JOINSwithout using keys or keys which are not unique enough.
JOINSon columns that have different column types.
- Using HEAP tables when not using a full key match with
- Forgetting a
DELETEin the MySQL monitor. If you tend to do this, use the
--i-am-a-dummyoption to the mysq client.
Different locks in MySQL
- Internal table locks.
LOCK TABLES(Works on all table types)
- Page locks (for BDB tables)
ALTER TABLEalso does a table lock on BDB tables.
LOCK TABLESgives you multiple readers on a table or one writer.
- Normally a
WRITElock has higher priority than a
READlock to avoid starving the writers. For writers that are not important one can use the
LOW_PRIORITYkeyword to let the lock handler prefer readers.
UPDATE LOW_PRIORITY 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 /*! SQL_BUFFER_RESULTS */ ...
SELECT SQL\_BUFFER\_RESULTS ...
- 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 STRAIGHT_JOIN ...
- 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:
LOCK TABLES trans READ, customer WRITE; SELECT sum(value) FROM trans WHERE customer_id=some_id; UPDATE customer SET total_value=sum_from_previous_statement WHERE customer_id=some_id; UNLOCK TABLES;
How to do a transaction with Berkeley DB tables:
BEGIN WORK; SELECT sum(value) FROM trans WHERE customer_id=some_id; UPDATE customer SET total_value=sum_from_previous_statement WHERE customer_id=some_id; COMMIT;
Note that you can often avoid transactions altogether by doing:
UPDATE customer SET value=value+new_value WHERE customer_id=some_id;
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 (...)
- 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.
--skip-locking(default on some OSes) if possible. This will turn off external locking and will give better performance.
application-levelhashed values instead of using long keys:
SELECT * FROM table_name WHERE hash = MD5(concat(col1,col2)) AND col_1='constant' AND col_2='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.
LEFT JOIN ... IF NULLoptimization.
CREATE TABLE ... SELECT
CREATE TEMPORARY table_name (...)
- Automatic conversion of temporary HEAP to MyISAM tables
- 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
RENAME TABLE foo as fooold, foonew AS foo)
- A query cache
- A better GUI client