How best to store and index all unique combinations of multiple integers in MYSQL?

I need to run a process against unique combinations of 3 to 8 values from a list of 25 integers. This creates a table with approximately 6-million unique record/options (assuming I limit queries as in the following chart) . The formula for unique combinations (here in excel form) for combinations of 8 from a 25 item row is = fact(25)/(fact(8)*fact(25-8)) ... See this explanation here ... https://www.khanacademy.org/math/precalculus/x9e81a4f98389efdf:prob-comb/x9e81a4f98389efdf:combinations/v/handshaking-combinations

As I want combinations of 3, 4, 5, 6, 7, or 8 - some records will have null entries for their fourth, through eighth values.

As multiple computers and processors might be working on this simultaneously, I need to store the list of "jobs" in MYSQL and update or delete each record when processed.

Using @Eggyal's solution. I can create a table for my 25 values and generate all unique combinations in MYSQL. This list of 25 to 50 values changes occasionally ...

CREATE TABLE UserContacts      (`contact_id` int)  ;        INSERT INTO UserContacts      (`contact_id`)  VALUES      (1),      (5),      (6)  ;  

To get all combinations, I can run ...

SELECT a.contact_id a, b.contact_id b, ... (I need up 6 of these combinations)  FROM   UserContacts a    JOIN UserContacts b ON b.contact_id > a.contact_id  

See it on sqlfiddle (change to MySQL 5.6 db for it to work).

My question is:

1. How to best add this data to a new MYSQL table that indexes these combinations properly, so I can find and update records from this table of millions of combinations?

2. Is it faster to create a compound primary key PRIMARY KEY (firstval, secondval, thirdval ...) ? If yes, how would a select or where query look for the compound solution? etc.

   CREATE TABLE 'combinations' (

      'combo_id' INT NOT NULL,       'firstval' SMALLINT(5) NULL,     'secondval' SMALLINT(5) NULL,     'thirdval' SMALLINT(5) NULL,     'fourthval' SMALLINT(5) NULL,     'fifthval' SMALLINT(5) NULL,     'sixthval' SMALLINT(5) NULL,     'seventhval' SMALLINT(5) NULL,     'eightthval' SMALLINT(5) NULL,       `started` TINYINT(1) NULL,     `score` TINYINT(3) NULL,     `added` DATETIME NULL,     `processed` DATETIME NULL,     PRIMARY KEY (`combo_id`)   )   ;  

https://stackoverflow.com/questions/67229070/how-best-to-store-and-index-all-unique-combinations-of-multiple-integers-in-mysq April 23, 2021 at 07:23PM