How can I improve query performance for 200+ million records

I would write your exists query like this:

SELECT stt.sgtin
FROM sgtins_tmp_table stt
WHERE EXISTS (SELECT 1
              FROM sgtins s
              WHERE s.client_id = 4 AND
                    s.sgtin = stt.sgtin
             );

For this query, you want an index on sgtins(sgtin, client_id).

i would suggest to rewite your EXISTS SQL as corelated subqueries tends to optimize badly most off the time.
The suggested query would be to use a INNER JOIN instead.

SELECT filter.sgtin
FROM (SELECT '<value>' AS sgtin UNION ALL SELECT '<value>' ..) AS filter
INNER JOIN sgtins ON filter.sgtin = sgtins.sgtin WHERE sgtins.client_id = 4

As most likely this is faster then using a temporary table.
But your are dealing with 50K values so i would make sense to generate the needed derived table SQL with dynamic SQL directly from the temporary table.

Also like i suggested in the chat.
Making a index (sgtins, client_id) would most likely make more sense depending on the data selectivity which is not really clear.
As that index might make your corelated subquery faster.

Query

# Maybe also needed to be changed with 50 K 
# SET SESSION max_allowed_packet = ??; 


# needed for GROUP_CONCAT as if defualts to only 1024 
SET SESSION group_concat_max_len = @@max_allowed_packet;

SET @UNION_SQL = NULL;

SELECT
  CONCAT(
       'SELECT '
    ,  GROUP_CONCAT(
          CONCAT("'", sgtins_tmp_table.sgtin,"'", ' AS sgtin')
          SEPARATOR ' UNION ALL SELECT '
       )
  )
FROM
 sgtins_tmp_table
INTO
 @UNION_SQL;


SET @SQL = CONCAT("
SELECT filter.sgtin
FROM (",@UNION_SQL,") AS filter
INNER JOIN sgtins ON filter.sgtin = sgtins.sgtin WHERE sgtins.client_id = 4
");


PREPARE q FROM @SQL;
EXECUTE q;

see demo

Editted because of comments

A more ideal approach would be using a fixed table which you index and use CONNECTION_ID() to separate the search values.

CREATE TABLE sgtins_filter (
    connection_id INT
  , sgtin varchar(255) NOT NULL
  , INDEX(connection_id, sgtin)
);

Then you can simply join between both tables

SELECT sgtins_filter.sgtin
FROM sgtins_filter
INNER JOIN sgtins
ON
    sgtins_filter.sgtin = sgtins.sgtin
  AND
    sgtins_filter.connection_id = CONNECTION_ID()
  AND 
    sgtins.client_id = 4; 

see demo

Assuming 200M rows and no more than 50K sgtins per client, there must be over 4K clients?

To benchmark with only 10 clients is risky. The Optimizer switches between using an index and doing a table scan in certain cases; this might be such a case.

So, please state the ultimate goal; I don't want to advise you one how to make a benchmark run faster, only to have the 'real' case not work with the advice.

Also, is the list of stgins static? You implied such by suggesting pre-building a MEMORY table. But this seems uncommon. Perhaps the 'real' case is given a different set of sgtins each time.

So, I will answer this question:

• 200M rows
• Table is more than 24GB
• innodb_buffer_pool_size = 24G
• Thousands of distinct client_id values. (With only 10, the Optimizer is tempted to ignore indexes and do a table scan.)
• Thousands of stgin values for each client_id
• The pair (client_id, stgin) is unique
• Each query may have a different list of stgins; that is, cannot assume the same list of stgins from run to run
• Want to optimize something like SELECT stgin FROM t WHERE client_id = 1234 AND stgin IN (..long list..)
• Want to optimize something like SELECT * FROM t WHERE client_id = 1234 AND stgin IN (..long list..)

Regardless of the numbers that EXPLAIN provides, the following is the optimal solution for both queries:

WHERE client_id = 1234 AND stgin IN (..long list..)`
PRIMARY KEY(client_id, stgin)   -- in this order.

Why?

• The optimizer is happy to focus on client_id = constant and hop through the list of stgins.
• By having client_id first in the PK, all the activity for a SELECT will be focused on a small fraction of the table. This is important because it limits the number of blocks to touch to less than buffer_pool_size.
• Technically, an independent INDEX(client_id, stgin) would be faster for the SELECT stgin..., but I don't recommend it, since it is so redundant, and won't save much performance.

Comments on Cost analysis:

• It does not take into account whether the block(s) are cached or not. With HDD drives, this can make a huge (10x) difference.
• It does not take much account of index vs data, nor index + data (as in a non-covering secondary index)
• It knows nothing about the distribution of values. (unless using MariaDB or MySQL 8.0, which have histograms)