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

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)

Languages

FromDual