Greetings everyone.
I'm faced with a database dilemma that involves two non-unique columns, language_id and product_id, which become unique when used together. I'm searching for a way to create a calculated field (integer) that is unique by using the values of these two non-unique columns.

One potential solution is utilizing bit shift to the left. However, I personally opted for UUID_SHORT() method to generate unique values for the calculated field.

Here's an example of how I implemented this:
SELECT
    UUID_SHORT() AS unique_index,
    language_id,
    product_id
FROM
    product_description;
In summary, the aim is to generate a unique calculated field from two combined non-unique columns.

language_id*1000000+product_id
product_id<1000000

Your solution utilizing the UUID_SHORT() method is indeed a viable one, especially when dealing with large datasets where uniqueness is crucial.

However, I'd like to offer an alternative solution that leverages the bit shift operation, as you initially considered. This approach can be more efficient and lightweight, especially when working with integer values. By using bit shift operations, you can combine the language_id and product_id columns to create a unique integer value.

Here's an example implementation:

SELECT
    (language_id << 16) | product_id AS unique_index,
    language_id,
    product_id
FROM
    product_description;
In this example, we're using a left shift operation (<<) to shift the bits of the language_id column 16 places to the left. This effectively creates a 32-bit integer with the language_id occupying the most significant 16 bits. We then use a bitwise OR operation (|) to combine the shifted language_id with the product_id column, which occupies the least significant 16 bits.

This approach assumes that the language_id and product_id columns are 16-bit integers or smaller. If your columns are larger, you may need to adjust the shift amount accordingly.

While your UUID_SHORT() method is a good solution, it may have some drawbacks, such as:

UUIDs are typically 128-bit values, which can be larger than necessary for a unique index.
UUIDs are randomly generated, which can lead to slower query performance due to the lack of sequentiality.
UUIDs may not be as human-readable as a simple integer value.
In contrast, the bit shift approach offers a more lightweight and efficient solution, with the added benefit of being more human-readable. However, it's essential to note that this method assumes a certain structure and size for the language_id and product_id columns, so be sure to verify that these assumptions hold true for your specific use case.

I'd recommend considering both solutions and evaluating their performance, readability, and maintainability in the context of your specific application.