Index counter shared by multiple tables in mysql

Question

I have two tables, each one has a primary ID column as key. I want the two tables to share one increasing key counter.

For example, when the two tables are empty, and counter = 1. When record A is about to be inserted to table 1, its ID will be 1 and the counter will be increased to 2. When record B is about to be inserted to table 2, its ID will be 2 and the counter will be increased to 3. When record C is about to be inserted to table 1 again, its ID will be 3 and so on.

I am using PHP as the outside language. Now I have two options:

1. Keep the counter in the database as a single-row-single-column table. But every time I add things to table A or B, I need to update this counter table.

2. I can keep the counter as a global variable in PHP. But then I need to initialize the counter from the maximum key of the two tables at the start of apache, which I have no idea how to do.

Any suggestion for this?

The background is, I want to display a mix of records from the two tables in either ASC or DESC order of the creation time of the records. Furthermore, the records will be displayed in page-style, say, 50 records per page. Records are only added to the database rather than being removed. Following my above implementation, I can just perform a "select ... where key between 1 and 50" from two tables and merge the select datasets together, sort the 50 records according to IDs and display them.

Is there any other idea of implementing this requirement?

Thank you very much

Answer 1

if you need creatin time wont it be easier to add a timestamp field to your db and sort them according to that field? i believe using ids as a refrence of creation is bad practice.

Answer 2

Well, you will gain next to nothing with this setup; if you just keep the datetime of the insert you can easily do

SELECT * FROM 
(
SELECT columnA, columnB, inserttime
FROM table1
UNION ALL
SELECT columnA, columnB, inserttime
FROM table2
) 
ORDER BY inserttime
LIMIT 1, 50

And it will perform decently.

Alternatively (if chasing last drop of preformance), if you are merging the results it can be an indicator to merge the tables (why have two tables anyway if you are merging the results).

Or do it as SQL subclass (then you can have one table maintain IDs and other common attributes, and the other two reference the common ID sequence as foreign key).

Answer 3

If you really must do this, there is a way. Create a one-row, one-column table to hold the last-used row number, and set it to zero. On each of your two data tables, create an AFTER INSERT trigger to read that table, increment it, and set the newly-inserted row number to that value. I can't remember the exact syntax because I haven't created a trigger for years; see here http://dev.mysql.com/doc/refman/5.0/en/triggers.html