Blog

So you have two versions of a database sitting around – maybe you’ve been trying things out on your development box, and now you want to move it all over to production. If it’s just a new table here and an extra column there, you could do it by hand, but if you’ve made lots of changes, it would be nice to let the computer do it for you.

Enter MySQL Workbenchmysqldiff.

CREATE TABLE t1 (id int);
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW 
  INSERT INTO t1 VALUES (NEW.id);

That’s not allowed; it won’t let you create the trigger.

CREATE TABLE t1 (id int);
CREATE TABLE f1 (id int) ENGINE=FEDERATED CONNECTION='localhost/test/t1';
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW 
  INSERT INTO f1 VALUES (NEW.id);

The server lets that through. Don’t do it.

On occasion I need to generate a number of rows. I don’t care about the contents, just how many rows there are. Maybe I want to create a table filled with a sequence of numbers. One common way to do this is to join a bunch of subqueries built out of unions:


SET @count := 0;
INSERT INTO table SELECT @count := @count + 1
FROM (SELECT 1 UNION SELECT 2) a
JOIN (SELECT 1 UNION SELECT 2) b
JOIN (SELECT 1 UNION SELECT 2) c
…

Of course, I could union more than two selects (e.g. SELECT 1 UNION SELECT 2 UNION SELECT 3). That makes each derived table longer, but reduces how many must be joined.

Question: What’s the optimal size of each derived table?

Defining “optimal”: since this is a one-off query, I don’t much care about performance (within reason), but I do care about how much typing I have to do. So I will favor “fewer characters” over “fewer joins”.

Each derived table in the query takes up 15 * numSelects + 2 characters.

To generate a given number of rows requires CEILING(LOG(numRows) / LOG(numSelects)) derived tables.

If I want 1000 rows in the result, I’d need 10 derived tables of two rows each (320 characters), or 7 of three rows (329 characters), or 3 of ten rows (456 characters).

For any given numRows, what’s the minimum of CEILING(LOG(numRows) / LOG(numSelects)) * (15 * numSelects + 2) ?

The answer varies with numRows – 2, 3, or 4 are alternately best when generating less than 130 rows, but any higher than that and the best choice usually turns out to be 3.

(SELECT 1 UNION SELECT 2 UNION SELECT 3)

A non-rigorous, non-scientific, totally off-the-cuff test of which function to pick when you need to group by year and month.

I populated a table with 262K rows of random dates, and then ran

SELECT %s, COUNT(*) FROM table GROUP BY %s ORDER BY NULL

with various functions, which should all result in the same grouping. I repeated each query five times and show the average time, using three different column types DATE, DATETIME, and TIMESTAMP.

And just in case you want to extract a full date (which really only makes sense for datetime and timestamp):

Dunno why you’d rather do this in SQL than in your application layer, but if you do, here’s one way to turn a delimited string of values back into multiple rows – just the opposite of GROUP_CONCAT:

SET @sourceString = 'a,b,c,d,e';
SET @sql = CONCAT('INSERT INTO t VALUES (\\'', REPLACE(@sourceString, ',', '\\'),(\\''), '\\')');
PREPARE myStmt FROM @sql;
EXECUTE myStmt;

Just to show what’s going on:

mysql> SELECT @sql;
+----------------------------------------------------+
| @sql                                               |
+----------------------------------------------------+
| INSERT INTO t VALUES ('a'),('b'),('c'),('d'),('e') |
+----------------------------------------------------+

It can be handy to index just part of a long character column. The strings might vary enough in just the first few characters to get the same cardinality out of a partial index as a full index would give, and the partial index takes up much less disk space and memory.

Indexes in The Manual

But how long should that prefix be? Should it be the same cardinality as the original data? Within 10%?

I dunno; somebody else can blog about that decision. I’m just interested in a query that finds the number which satisfies your requirements.

I have a table called ‘numbers’ with an int field ‘num’ that just holds values 1 through a large number; for this case, it needs to be no larger than the length of the longest string in the table.

Then:

SELECT
  numbers.num AS prefixLength,
  dt.originalCardinality,
  (SELECT COUNT(DISTINCT LEFT(stringField, numbers.num)) FROM yourTable) AS prefixCardinality
FROM
  numbers
  JOIN (SELECT COUNT(DISTINCT stringField) AS originalCardinality FROM yourTable) AS dt
WHERE
  (SELECT COUNT(DISTINCT LEFT(stringField, numbers.num)) FROM yourTable) >= .90 * originalCardinality
ORDER BY numbers.num
LIMIT 1;