Just picked up a nice tid-bit on creating a unique index on a two column table where the values in each column may be either way around but you only ever want one instance of the value in that row. So what this means is, inserting 2,1 and 1,2 for example would result in only the first of the two inserts succeeding.
CREATE UNIQUE INDEX ON tablename (LEAST(col1,col2), GREATEST(col1,col2));
Also, WITH, I’ll be honest, never thought about using it to create temporary views. This is a bad example but shows the structure rather well:
WITH tempView (a,b) AS (
SELECT table1.col1, table2.col2
FROM table1
LEFT JOIN table2
ON table1.id=table2.id
)
SELECT a,b FROM tempView;
Better yet is changing this to WITH RECURSIVE tempView and then adding in a select inside the WITH that recalls tempView. The great example he gave is for getting flights from A to B with a varying amount of stops, it would be possible to get all routes from A to B with one MySQL query, as long as the data stored all connecting routes.
Incidentally, while there is some great stuff coming out of this RDBMS talk, I think the queries are really hurting a lot of people’s heads. Good stuff though.
