Mutex tables in SQLThu, Sep 22, 2005 in Databases
A “mutex” table is a clever trick that allows joining tables while keeping them independent of each other in a query. This property allows interesting queries that are not otherwise possible. It is especially useful in earlier versions of MySQL, where it can be used to simulate some unsupported queries such as UNION and derived tables in the FROM clause.
My standard mutex table is as follows:
create table mutex( i int not null primary key ); insert into mutex(i) values (0), (1);
I typically use the mutex table as the leftmost table of
LEFT OUTER JOIN queries. For examples, see my articles about simulating
UNION, FULL OUTER JOIN on MySQL, and simulating derived tables in the FROM clause. I add as many values for
i as needed, usually one per table in the
If you insert values 0 through 9, the table can do double duty as an integers table, too. Alternatively, an existing integers table can be used as a mutex table.
I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and many open-source tools for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB.