MySQL – One Auto Incremented ID for Two Separate Tables

auto-incrementMySQLsequence

I have 2 different tables, each having its own id column.

I need that if one table is using a specific id, the other table will not use it. This is needed because the object in both tables will later be handled together by another component in the application, and the other component must have them uniquely identified by the id.

I thought to have a table counter with next_id column, and have a before insert trigger to each of these tables, that will take the current id from counter, use it for the new row, and increase it.

My question is, is this synchronized? i.e. what will happen if 2 different applications, or threads in a single application, insert columns to any of these tables?

Do I have to lock the counter table during the trigger? If yes, will it be locked until the end of the entire transaction, or just until the trigger ends?

Is there a better solution for this problem?

Best Answer

If the two tables share ID space, surely then they should have a common parent table?

I would create a parent table and make the two other tables children of it with foreign keys. The parent has the autoincrement field, and you have to insert there first before inserting the child. Add a "type" column to the parent table so you know in which table to look for the child with the given ID.

The biggest advantage to this approach is that you don't need any triggers. No performance or synchronicity problems to worry about.

In PostgreSQL you would implement this using inheritance, but I don't think MySQL has this feature.

Related Solutions

MySQL Database Design – Separate Tables vs Partitioning One Huge Table

I have an suggestion regarding your second option.

If you need to keep the MyISAM tables with each unique my_column value separated from other MyISAM tables, you may want to look into the MERGE (Mrg_MyISAM) Storage Engine as an alternative to table partitioning.

This will allow your multiple MyISAM tables within the same database that have identical table structures and index layout to be mapped together in such a way that single query hits all MyISAM tables.

Suppose you have a table as follows:

CREATE TABLE tb1
(
    my_column INT NOT NULL
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(30),
    PRIMARY KEY (my_column,id),
    KEY name (my_column,name)
) ENGINE=MyISAM;

and you have 3 such tables (tb1, tb2, tb3) where

my_column for tb1 = 1
my_column for tb2 = 2
my_column for tb3 = 3

You can merge them under a single table mapping as follows:

CREATE TABLE tbmerge LIKE tb1;
ALTER TABLE tbmerge ENGINE=Mrg_MyISAM UNION=(tb1,tb2,tb3);

To perform a search through all the tables, just use tbmerge. For example, suppose you want to see every name from tb2 that starts with 'Jack', you run this query:

SELECT name FROM tbmerge WHERE my_column=2 AND name LIKE 'Jack%';

Given the design of the table, you should always specify the value for my_column. In fact, for every index tb1 has, make sure my_column is always the first column. The reason? A query against tbmerge is always a query against tb1, tb2, tb3 (all underlying tables). Otherwise, this query

SELECT name FROM tbmerge WHERE name LIKE 'Jack%';

will experience horrible performance because it will perform table scans against all underlying tables. Please plan carefully the indexes you will be using, following that simple rule (using my_column as the first column of every index)

There is an additional benefit: You can INSERT into the underlying tables at your convenience, thus working with the

I discussed this using MERGE tables in an earlier post (Jan 4, 2012).

Mysql – How to sync two MySQL high load tables with 10M rows

pt-table-sync from Percona Toolkit will do this sort of thing.

Best Answer

Related Solutions

MySQL Database Design – Separate Tables vs Partitioning One Huge Table

Mysql – How to sync two MySQL high load tables with 10M rows

Related Question