Hint

Table of Contents

• Accounts not properly closing connections
• Unused indexes
• Who created temporary (disk) tables
• Accounts which never connected since last start-up
• Users which never connected since last start-up
• Totally unused accounts (never connected since last restart and not used to check Stored Program or View privileges) since last start-up
• SHOW FULL PROCESSLIST
• Storage Engines per schema
• Tables without a Primary Key
• Bad SQL queries of users
• SHOW PROFILE in PERFORMANCE_SCHEMA
• SELECT, INSERT, UPDATE and …

At a customer we had a nice example of how the MySQL Optimizer is cheating when used in combination with the MySQL Cluster. The customer had queries running not too slow in the development environment but when he tried them on the acceptance test environment (with more data) the query was running much too long which was unacceptable because this query can occur many times per second.

What has happened?

First of all we had a look at the execution plan of the query generated by the MySQL Optimizer:

EXPLAIN …

Table of Contents

• Result set with temporary sequence
• Determination of optimal length of prefixed indexes
• Using MySQL keywords in table or columm names
• Missing Primary Key Index
• Problems while installing a MySQL 5.5 database
• InnoDB AUTO_INCREMENT at 2^nd position

Result set with temporary sequence

Sometimes you would like to have a result set with something like a rownum. You can do this at least in the following two ways:

a) with a TEMPORARY MEMORY table:

CREATE TEMPORARY TABLE mem (
    seq  INT NOT NULL …