Mysql – Creating a single recid stream for multiple tables

auto-incrementmariadbMySQL

I'd like to have a single reservoir of RECIDs to apply across multiple tables.

Obviously I can do that by creating a table with a single auto-increment field and inserting every time I need a new RECID.

But that produces a lot of 8-byte+ records that have no real purpose.

A little resident function to delete each record when produced would keep the table pruned back, but I'm wondering whether there's a cleaner way to get to the same goal.

Best Answer

You don't need a "reservoir", rather a way to generate a unique number.

Usually it is sufficient to give that task to one table you already have, and use AUTO_INCREMENT there, plus LAST_INSERT_ID().

But it you really need a sequence generator:

http://forums.mysql.com/read.php?10,616636,617041 (Simulating sequences)
http://dev.mysql.com/doc/refman/5.7/en/information-functions.html (search for "sequence")
http://forums.mysql.com/read.php?10,252807,262654 (A sequence solution)

Related Solutions

Mysql – How to optimize multiple self-JOINs

I don't think there is a need for denormalizing the table. Self-joining will work fine if you have indexes that will be used effectively. For the specific query, I'd add an index on (year, dataID, countryID, data) and not use any derived tables:

SELECT 
    c.countryName AS Country,   
    em01.data AS data01,
    em02.data AS data02,
    ...
    emXX.data AS dataXX
FROM  
    Countries AS c
      JOIN CountryData AS em01
        ON  em01.year = 2017
        AND em01.dataID = 523 
        AND em01.countryID = c.countryID 
      JOIN CountryData AS em02
        ON  em02.year = 2017
        AND em02.dataID = 524
        AND em02.countryID = c.countryID
      ... 
      JOIN CountryData AS emXX
        ON  emXX.year = 2017
        AND emXX.dataID = YYY
        AND emXX.countryID = c.countryID 
 ;

The only issue you'll have is that MySQL has a hard limit of maximum 61 joins in a query. So, you won't be able to have 90 columns with the above query.

Another observation is that you don't seem to need any aggregated data but just a tiny subset of the (small or big table, doesn't matter). With the suggested index, you could just write a query like this:

SELECT 
    cd.countryID,
    c.countryName AS Country,   
    cd.dataID,
    cd.data
FROM  
    Countries AS c
      JOIN CountryData AS cd
        ON  cd.countryID = c.countryID 
WHERE 
      cd.year = 2017
  AND cd.dataID IN (522, 523, ..., YYY) 
ORDER BY
    cd.countryID,
    cd.dataID ;

and have the pivot transformation done in your application.

MySQL – Performance Impact of Not Using Foreign Key

There is no performance impact of not using a foreign key. A foreign key constraint simply enforces referential integrity by constraining the values that can be inserted into the foreign key column (s).

If you are using UUIDS as identifiers, why do you need the type columns in the association table?

As you mention modelling this in the traditional manner would result in a large number of tables. Deleting, for example, a user would result in the RDBMS checking each foreign key constraint to ensure non are violated, which could take some time! With your proposed approach the RDBMS would have no foreign key constraints to check and the user would be deleted quicker. So you actually get better performance deleting objects. As you noted you'll have to have a background job that makes the association table eventually consistent. Regarding this, you could consider keeping a log of deleted objects and replaying it against the associations table. This would be better performing than carrying out a existence check on each row to see if the referenced objects still exist.

I've used this approach for several years now to allow anything to relate to anything else and haven't encountered any issues.

Best Answer

Related Solutions

Mysql – How to optimize multiple self-JOINs

MySQL – Performance Impact of Not Using Foreign Key

Related Question