You are here

MySQL hints

Content

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 AUTO_INCREMENT PRIMARY KEY
  , data VARCHAR(32)
) ENGINE=MEMORY;

INSERT INTO mem
SELECT NULL, data
  FROM test
 LIMIT 5;

SELECT *
  FROM mem;

+-----+------+
| seq | data |
+-----+------+
|   1 | abc  |
|   2 | def  |
|   3 | ghi  |
|   4 | abc  |
|   5 | def  |
+-----+------+

b) with a user defined variable
SET @seq=0;
SELECT @seq:=@seq+1, data FROM test WHERE id < 100 LIMIT 5;
+--------------+------+
| @seq:=@seq+1 | data |
+--------------+------+
|            1 | abc  |
|            2 | def  |
|            3 | ghi  |
|            4 | abc  |
|            5 | def  |
+--------------+------+

But be cautious with playing around:

SET @seq=0;
SELECT @seq:=@seq+1, data FROM test
 WHERE id < 100 GROUP BY 2, 1 LIMIT 5;
+--------------+------+
| @seq:=@seq+1 | data |
+--------------+------+
|            1 | abc  |
|            4 | abc  |
|            7 | abc  |
|           10 | abc  |
|           13 | abc  |
+--------------+------+

Determination of optimal length of prefixed indexes

For CHAR, VARCHAR, BINARY, and VARBINARY columns, indexes (called prefixed indexes) can be created that use only part of a column, using col_name(length) syntax to specify an index prefix length.

These indexes are shorter and thus safe space (on disk and in memory) and can be faster than non prefixed indexes.

But shortening indexes can reduce cardinality (number of distinct values) of an index and is thus worse.

With this statement you can find out the optimal length of an prefixed index. Optimal means close than or equal cardinality to the full index.

SELECT COUNT(DISTINCT LEFT(my_column, <n>)) card
  FROM my_table;

Let's assume that we have an index on my_column VARCHAR(32) with a cardinality of 1142 we can say after some trials (increasing n from 1 to ...), that a prefixed index with more than 6 characters length does NOT make sense with the present data (except when we retrieve the data from the index only (index look up)).

+---+-------+
| n | card  |
+---+-------+
| 4 |  258  |
| 5 |  741  |
| 6 | 1142  |
+---+-------+

Let's assume, that we have approx. 1 Mio rows in this table with an utf8 character set (3 bytes per character) then the original index has a size of 97 Mio bytes (1 Mio x (1 + 3 x 32)). But our prefixed index has a size of only 19 Mio bytes (1 Mio x (1 + 3 x 6)). This is a gain of approx 80% of space (and also some performance)!

Using MySQL keywords in table or column names

MySQL prevents the usage of MySQL keywords (reserved words) for table and column names.

But in certain circumstances it is necessary or wanted to use them:

CREATE TABLE `by` (id INT, data VARCHAR(255));

INSERT INTO by VALUES (1, 'test');

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'by
values (1, 'test')' at line 1

INSERT INTO `by` VALUES (1, 'test');

Query OK, 1 row affected (0.00 sec)

SELECT * FROM `by`;

This can be reached by using back-ticks (back quotes "`", ASCII(96)).

Missing Primary Key Index

When creating a child table with a foreign key on a parent table you will run into the following error when you do NOT have an index on the parents primary key attribute.

CREATE TABLE parent (
    id   INT NOT NULL
  , data VARCHAR(32)
) ENGINE=InnoDB
;

CREATE TABLE child (
    id   INT NOT NULL
  , data VARCHAR(32)
  , fk INT
  , CONSTRAINT fk_c FOREIGN KEY (fk) REFERENCES parent (id)
) ENGINE=InnoDB
;
ERROR 1005 (HY000): Can't create table 'test.child' (errno: 150)

ALTER TABLE parent
  ADD PRIMARY KEY (id)
;

CREATE TABLE child (
    id   INT NOT NULL
  , data VARCHAR(32)
  , fk INT
  , CONSTRAINT fk_c FOREIGN KEY (fk) REFERENCES parent (id)
) ENGINE=InnoDB
;
Query OK, 0 rows affected (0.08 sec)

Problems while installing a MySQL 5.5 database

When you want to install a new MySQL 5.5 database as follows:
./scripts/mysql_install_db --datadir=/home/mysql/data/mysql-5.5.8/

you might run into the following error:

Installing MySQL system tables...
./bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

It looks like MySQL uses on Linux the Asynchrous I/O support [ http://lse.sourceforge.net/io/aio.html ]. To fix this problem you have to install the Asynchronous I/O library.

On Ubuntun/Debian:
shell> sudo apt-get install libaio1

On OpenSuse/SLES:
shell> rpm -ivh libaio-<some-version>.rpm

On Centos/RHEL:
shell> yum install libaio

InnoDB AUTO_INCREMENT at 2nd position

In InnoDB it is not allowed to have an AUTO_INCREMENT value other than at the first position of the Primary Key. An AUTO_INCREMENT column must appear as the first column in an index on an InnoDB table. [ 1 ] This is for example a problem when you migrate from MyISAM to InnoDB. Further in InnoDB the Primary Key influences the sort order of the rows. If you want to have a different sort order than the AUTO_INCREMENT value (which reflects a sorting by time) for example by user_id then you cannot do this. When you read the documentation carefully you will not see that a Primary Key is mentioned but just: as the first column in an index. With the following work-around we show you how you can achieve it anyway:
CREATE TABLE test_1 (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  aid  INT UNSIGNED NOT NULL,
  data VARCHAR(64) DEFAULT NULL,
  PRIMARY KEY (id, aid)
) ENGINE=INNODB;

CREATE TABLE test_2 (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  aid  INT UNSIGNED NOT NULL,
  data VARCHAR(64) DEFAULT NULL,
  PRIMARY KEY (aid, id)
) ENGINE=INNODB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

CREATE TABLE test_3 (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  aid  INT UNSIGNED NOT NULL,
  data VARCHAR(64) DEFAULT NULL,
  PRIMARY KEY (aid, id),
  UNIQUE KEY (id, data)
) ENGINE=INNODB;

The output for table 1 and 3 of the InnoDB Table Monitor looks as follows:
--------------------------------------
TABLE: name test/test_1, id 0 51, columns 6, indexes 1, appr.rows 3
  COLUMNS: id: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4;
          aid: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4;
         data: DATA_VARCHAR prtype 524303 len 64;
    DB_ROW_ID: DATA_SYS prtype 256 len 6;
    DB_TRX_ID: DATA_SYS prtype 257 len 6;
  DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
  INDEX: name PRIMARY, id 0 85, fields 2/5, uniq 2, type 3
   root page 54, appr.key vals 3, leaf pages 1, size pages 1
   FIELDS:  id aid DB_TRX_ID DB_ROLL_PTR data
--------------------------------------
TABLE: name test/test_3, id 0 52, columns 6, indexes 2, appr.rows 3
  COLUMNS: id: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4;
          aid: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4;
         data: DATA_VARCHAR prtype 524303 len 64;
    DB_ROW_ID: DATA_SYS prtype 256 len 6;
    DB_TRX_ID: DATA_SYS prtype 257 len 6;
  DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
  INDEX: name PRIMARY, id 0 86, fields 2/5, uniq 2, type 3
   root page 55, appr.key vals 3, leaf pages 1, size pages 1
   FIELDS:  aid id DB_TRX_ID DB_ROLL_PTR data
  INDEX: name id, id 0 87, fields 2/3, uniq 2, type 2
   root page 56, appr.key vals 3, leaf pages 1, size pages 1
   FIELDS:  id data aid

An other possibility is to use a sequence instead of an AUTO_INCREMENT value:
CREATE TABLE sequence (
  sequence_name VARCHAR(32) NOT NULL,
  `value` INT NOT NULL,
  PRIMARY KEY (sequence_name)
) ENGINE=InnoDB;

INSERT INTO sequence VALUE ('Test Sequence', 1);

START TRANSACTION;
UPDATE sequence SET value = value+1 WHERE sequence_name = 'Test Sequence';
SELECT value INTO @seq FROM sequence WHERE sequence_name = 'Test Sequence';
INSERT INTO target (id, inc, data) VALUES (3, @seq, 'bla');
COMMIT;

SELECT * FROM sequence;
SELECT * FROM target;