1

Here is an example of 2 extract from the same table:

SELECT source_id 
FROM table_cust_string_value
WHERE cust_string_id=2 AND VALUE LIKE '%TATA%';

SELECT source_id 
FROM table_cust_string_value
WHERE cust_string_id=4 AND VALUE LIKE '%TUTU%';

They give 2 sets of source_id.

Right. Now if I need an intersect of those with MySQL (where INTERSECT does not exist) I found this way:

SELECT DISTINCT source_id 
FROM (
   SELECT source_id 
   FROM table_cust_string_value
   WHERE cust_string_id=2 AND VALUE LIKE '%TATA%'
   ) t1
INNER JOIN (
   SELECT source_id 
   FROM table_cust_string_value
   WHERE cust_string_id=4 AND VALUE LIKE '%TUTU%'
   ) t2
USING (source_id);

but what if I need to do this from N sets ?

I can't find a solution + I'm worried about the perf. of doing it this way

Maike
  • 148
  • 6
  • 3
    You're using `LIKE '%pattern%'` and you're concerned about performance? I am skeptical. Doing unindexable pattern-searching is probably more harmful to performance than doing joins. – Bill Karwin Jan 22 '22 at 23:18
  • Well... I agree on that but it's a requirement and I don't know other ways to search partial text in fields. Do you ? Anyway, I think cumulating counter-performances is not desired though so I'm searching for a fix for the second one. – Maike Jan 23 '22 at 21:22
  • If you need to optimize performance for querying certain words, then you should store a column that contains only the substring you're searching for, 'TATA' and 'TUTU', etc. Then define an index on that column, and optimize searches with `=` instead of `LIKE` with wildcards. – Bill Karwin Jan 23 '22 at 22:15

1 Answers1

1

You can use a grouping approach. Depending on what indexes you have available this might work out better.

SELECT source_id
FROM   table_cust_string_value
WHERE  cust_string_id IN ( 2, 4 )
GROUP  BY source_id
HAVING MAX(CASE WHEN cust_string_id = 2 AND VALUE LIKE '%TATA%' THEN 1 END) = 1
       AND MAX(CASE WHEN cust_string_id = 4 AND VALUE LIKE '%TUTU%' THEN 1 END) = 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845