You are here
MySQL hints
Content
- 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 2nd 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 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;