0

Problem:

Assuming I have a table like this:

CREATE TABLE `test` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `a_id` bigint unsigned NOT NULL,
  `b_id` bigint unsigned NOT NULL,
  `c_id` bigint unsigned NOT NULL,
  `d_id` bigint unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_entry` (`a_id`,`b_id`,`c_id`,`d_id`),
  KEY `test_b_id` (`b_id`),
  KEY `test_c_id` (`c_id`),
  KEY `test_d_id` (`d_id`)
)

In this test table, every id can appear multiple times, but all combinations must be unique.

Now I want to write an application to compare them. I don't want to compare apples with peaches, so I have to find entries where all other keys are the same and only one of them is different.

How can I archive this and what indexes would be useful here? Let's say comparisons on column a_id would be significantly more common than on all other columns.

There is no need to find this for all 4 columns in one query. I would like to have a template query to find comparable entries for one of the columns.


Example:

Assume there are 10 different ids for each column and every combination of them is present in the database. (10^4 = 10000 entries).

Query 1: Now I want to compare a_id = 1 with a_id = 2. The query result has to look something like this:

Test: [a_id, b_id, c_id, d_id]
1, 1, 7, 1
2, 1, 7, 1
1, 5, 5, 3
2, 5, 5, 3
1, 1, 7, 4
2, 1, 7, 4
⋮

Notice a_id is in (1, 2) but for each entry, there is a matching one, where only a_id is different.

Query 2: Also, I would like to provide a way to query which comparisons could be made on a specific column. The result should look like Query 1 but for arbitrary ids. (id_a in (m, n))

In my case the database does only contain a subset of all possible combinations.


One idea:

From my understanding, unique composite keys are like an additional unique column where the value is a chain of the original columns in the specified order.

fname, lname, composite_key(lname, fname)
---------------------------
John, Miller, Miller-John
Peter, Jones, Jones-Peter
…

If it is possible to query on this composite_key index tree or something like that, my problem would be easy to solve. Create a composite index for each column so every column is the last one once. E.g.

KEY `key_a` (`b_id`, `c_id`, `d_id`, `a_id`),
KEY `key_b` (`a_id`, `c_id`, `d_id`, `b_id`),
KEY `key_c` (`a_id`, `b_id`, `d_id`, `c_id`),
KEY `key_d` (`a_id`, `b_id`, `c_id`, `d_id`),

For a column a_id comparison just find all paths on the tree with two values on the last branch. This would be similar for the other columns.

2 Answers 2

1

Task 1 may be solved by

SELECT t1.*
FROM test t1
JOIN test t2 USING (b_id, c_id, d_id)
WHERE (t1.a_id, t2.a_id) IN ((1,2), (2,1))
1
  • Thank you very much. This is a great first step. Commented Apr 13, 2021 at 12:46
0

Without much context (if you provide some query examples, then we can better visualize what indexes would be best), creating an index on (a_id, b_id, c_id, d_id) would probably be sufficient, based on what you mentioned. It would cover you for both types of queries, the more common ones that only compare a_id, and would also cover you for comparisons on all four columns simultaneously as well.

Additionally it would cover you on any contiguous subset of the columns in that index as well such as queries that compare on a_id and b_id, or a_id, b_id, and c_id.

1
  • 1
    I provided an example. I hope this clarifies my question. Commented Apr 13, 2021 at 11:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.