Temporary

MySQL temporary tables are created either in memory (as MEMORY tables) or on disk (as MyISAM tables). How many tables went to disk and how many tables went to memory you can find with:

mysql> SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Created_tmp_disk_tables | 49094    |
| Created_tmp_tables      | 37842181 |
+-------------------------+----------+

Tables created in memory are …

FreeBSD 9 seems to have some troubles with MySQL 5.5.20. A customer has moved from MySQL 5.0 on Linux to MySQL 5.5 on FreeBSD 9. He experienced a lot of periodic slow downs on the new, much stronger, system which he has not seen on the old Linux box.

This slow downs were also shown in high CPU system time but we could not see any I/O going on.

When we looked into MySQL we have seen many threads in Opening tables state in the MySQL processlist.

The first idea was to increase table_open_cache to 2048 and …

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 …