A Little Noise

Month: August 2006

  • Auto_increment Indexing

    It’s so common to set the auto_increment column to primary key, it has become common belief that it’s required. Not true; an auto_increment column can use any index type. It doesn’t even have to be unique:

    CREATE TABLE testTable (
  id INT AUTO_INCREMENT,
  INDEX(id)
) ENGINE=MyISAM; -- Works for InnoDB too.

INSERT INTO testTable VALUES (1), (1), (NULL);

SELECT * FROM testTable;

+----+
| id |
+----+
|  1 |
|  1 |
|  2 |
+----+

  • Order By Puzzle

    From a question on Freenode.

    SELECT * FROM theTable;
    +----+----------+
| id | data     |
+----+----------+
|  1 | middle   |
|  2 | first    |
|  3 | showLast |
+----+----------+

    Fair enough. Let’s order it by `data`.

    SELECT * FROM theTable ORDER BY data;
    +----+----------+
| id | data     |
+----+----------+
|  3 | showLast |
|  1 | middle   |
|  2 | first    |
+----+----------+

    What??? Maybe the server didn’t quite understand.

    SELECT * FROM theTable ORDER BY data ASC;
    +----+----------+
| id | data     |
+----+----------+
|  3 | showLast |
|  1 | middle   |
|  2 | first    |
+----+----------+

    How did that happen? I assure you I have not switched to some ‘reverse order’ collation.

    SHOW CREATE TABLE theTable;
    CREATE TABLE `thetable` (
  `id` int(11),
  `data` enum('showLast','middle','first')
)

    Ah. `data` is an enum. ORDER BY on an enum orders by the enum index, not the string. You can order by CONCAT(data) or by CAST(data AS char). Are there better ways?

  • LAST_INSERT_ID and multi-row inserts

    A bit of a quiz to see if you’re paying attention:

    First, we’ll set up a couple tables with some auto increment fields:

    CREATE TABLE t1 (id INT AUTO_INCREMENT, INDEX(id));
CREATE TABLE t2 (id INT AUTO_INCREMENT, refT1 INT, INDEX(id));

    Next, we’ll insert a row to one table:

    INSERT INTO t1 VALUES (NULL);

    Then, we’ll see what happens when we insert two rows using LAST_INSERT_ID():

    INSERT INTO t2 VALUES (LAST_INSERT_ID(), 1), (LAST_INSERT_ID(), 2);

    What happens here? Did the insert go ok, or did we get an error? What happens if we now issue

    SELECT * FROM t2;

    No errors, because we defined the auto_increment field for t2 not as a primary or unique key, but just with index. Remember that LAST_INSERT_ID() only picks up the last value you inserted using NULL, and not explicitly defined values. So we get two copies of the value ‘1’ in our id field.

    +----+-------+
| id | refT1 |
+----+-------+
|  1 |     1 | 
|  1 |     2 | 
+----+-------+

  • Top N of a Group

    No doubt everybody has solved it before, but ten seconds on Google didn’t show any obvious results, so here we go: How to pick the top 5 records of each group.
    (more…)

  • Staging Table

    Can MySQL handle a staging table, one where a fixed number of rows are reused in a round-robin basis (e.g., temporarily store AJAX-driven auto-saved forms, without overwriting the real values so the user can still abandon changes), without an external script?

    First thoughts turned to events, triggers, stored procedures, and such toys in the most recent versions of MySQL – none of which worked quite right. Turns out we can do it with a little one-time setup and a single statement that will work as far back as version 4.0.4.
    (more…)

  • Sum top N of a group

    Trying to sum the top 5 entries in each group.

    SELECT
    theId,
    SUM(theData) FROM (
        (SELECT
            @count := 0, 
            @oldId := 0 AS theId, 
            0 AS theData) 
        UNION 
        (SELECT 
            @count := if(@oldId = id, @count+1, 0), 
            @oldId := id, 
            if(@count < 5, a, 0) 
        FROM 
            (SELECT * FROM theTable ORDER BY id, a DESC) AS theTable 
        ) 
    ) AS theView 
WHERE theId != 0 
GROUP BY theId;

    Table population:

    INSERT INTO `thetable` VALUES 
    (1,1), (1,2), (1,3), (1,4),
    (1,5), (1,6), (1,7), (2,1),
    (2,2), (2,3), (2,4), (2,5);