0

The given MySQL join table, rel_d:

Columns (all of them are unsigned integers, none of the are unique):

  • item_id (16 bit)
  • ds_id (16 bit)
  • ditem_id (8 bit)

rel_d contains cca. 700K rows, but this number is quickly growing (thousands of rows per day added).

I haven't created primary index. This table can be only queried by the columns above because of the content and the nature of the table.

The goal is the quickest method (memory consumption is irrelevant) to find the rows which match the following kind of complex criteria:

ds_id == 'arg_ds'

and

(ditem_id == 'arg_di1' or ditem_id == 'arg_di2' or ditem_id == 'arg_di3' or ...)

and item_id have to be the same within a given ditem_id argument group

For example if the criteria is:

  • ds_id = 193
  • ditem_id == 128
  • ditem_id == 131

I need those item_ids with ds_id = 193 which have ditem_id == 128 and ditem_id == 131 too. Those item_ids where only one of the ditem_ids is present should be excluded.

Expected result without grouping:

item_id ds_id   d_item_id
1230        193     128
1230        193     131
2345        193     128
2345        193     131
2346        193     128
2346        193     131

Useful part of the expected result:

item_id
1230
2345
2346

Could you please help me with constructing a quick SELECT statement which satisfies the requirements above?

Hemi81
  • 578
  • 2
  • 15
  • 34
N. Mate
  • 1
  • 1
  • I'm afraid none of the solutions featured in the related topic could solve my problem. SELECT item_id FROM rel_d WHERE ditem_id in (128, 131) and ds_id = 193 HAVING COUNT(*) = 2 returns only the first occurrence. There should be thousands of rows. – N. Mate Oct 24 '15 at 09:00
  • **Success**: this is the combination of the two queries: `SELECT item_id FROM rel_d WHERE ds_item_id = 193 AND descriptor_item_id in (128, 131) group by item_id HAVING COUNT(*) = 2` This definitely works. Thank you! – N. Mate Oct 24 '15 at 09:14

0 Answers0