Masao Kitamura

This usually happens when you’re doing massive queries on InnoDB tables:

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

In /etc/my.cnf, do something like this:

innodb_buffer_pool_size=524288000

By default, MySQL sets innodb_buffer_pool_size to 8MB.

500 megabytes = 524 288 000 bytes

ALTER TABLE mytable
ADD mycolumn INT UNSIGNED NOT NULL AFTER othercolumn ,
ADD INDEX ( mycolumn );

Slow Query Example

SELECT
    widget_id
  , widget_name
  , COUNT(DISTINCT type) AS num_types
FROM widget
GROUP BY type
ORDER BY num_types DESC

With lots (>100K) of rows, this query will get slow because it orders on an aggregate COUNT DISTINCT function. Remove the ORDER BY to make this fast again.

When optimizing a query, the order of fields matter in a multi-field index.

In general, you should use the most selective fields first.

For example, in a hypothetical location index, use zip, city, state, rather than the reverse.

Source: MySQL Forums, Jay Pipes, Performance Tuning Best Practices Presentation

1. Use UNSIGNED INT (or BIGINT if needed) for primary key, since you don’t need negative primary keys.
2. Don’t use ON DELETE CASCADE, since you might delete rows you wanted to keep.  Explicitly delete rows in your code.
3. Don’t use ON UPDATE CASCADE, since you might want to change the ID in the parent table of the row being referenced, without changing the ID of the child row.
4. Always use foreign keys to constrain your data and require that foreign keys reference rows that actually exist.
5. Use UNIQUE KEYS where necessary and where they conceptually make sense.  All fields in the UNIQUE KEY must be NOT NULL for the key to be effective.
6. Use singular table names, unless a row represents a plural object.  (A table of chairs would be called “chair”, unless a row represented a set of “chairs”).  This makes code generation easier (esp. for tables named “category”, “specialty”, “country”, etc)

Update:

Keep data types as small as possible for what you need
• Don’t use BIGINT unless you have to! Waste of space.
• Especially true on indexed columns
• Fundamental principle: The smaller your data types, the more records will fit into the index blocks. The more records fit in each block, the fewer reads are needed to find your records.

Source: Jay Pipes, MySQL Presentation, Index Coding Optimization

• Smaller is better – use the smallest data type necessary.  When in doubt, use the smallest one you don’t think you’ll exceed.
• Avoid NULL if possible – Nullable columns make indexes, stats, and value comparisons more complicated.
• Use simpler data types when possible – use integers for IP addresses
• For storage and computation, INT(1) and INT(20) are equivalent.  The numeric is for display purposes only for applications such as the command line client.
• Use FLOAT over DECIMAL for speed – the CPU performs the computations natively, as opposed to the server making the DECIMAL calculation

Source: O’Reilly High Performance MySQL

Instead of:


ALTER TABLE my_table ADD KEY `key1` (`apple`);
ALTER TABLE my_table ADD KEY `key2` (`banana`);


do this:


ALTER TABLE my_table
ADD KEY `key1` (`apple`),
ADD KEY `key2` (`banana`);


Add a user “nobody” only accessible via “localhost” with no password and only “usage” privileges.

If you look at the MySQL log, you will see the munin client accessing MySQL via the “nobody” user.

To test the munin client, connect to your client via telnet:

$ telnet localhost 4949

Move a column after some other column

mysql> ALTER TABLE mytable MODIFY COLUMN mycolumn INT AFTER someothercolumn;

Move a column to the first position

mysql> ALTER TABLE mytable MODIFY COLUMN mycolumn INT FIRST;

Use EXPLAIN to show which indexes are being used.

mysql> EXPLAIN SELECT mycolumns FROM table1 LEFT JOIN table2;\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ag
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1010
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: zsg
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 361
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: ads
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: database.table.column
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: account_networks
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: database.table.column
rows: 1
Extra:
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: accounts
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: database.table.column
rows: 1
Extra:

…
6 rows in set (0.00 sec)