Index optimization for IN, BETWEEN, ORDER BY and LIMIT query
Shrinking the table size will help performance:


Don't use BIGINT unless you expect to need more than 4 billion ids; INT UNSIGNED takes half the space.
Don't use INT (4 bytes) for low-cardinality flags/types/levels(?)/etc.  ENUM or TINYINT UNSIGNED is only 1 byte.


Write a little more code to avoid useless parts to the WHERE, such as AND filter.metrcs_1 BETWEEN 0 AND 9999999999.

Pagination via OFFSET (LIMIT 5000, 50) is problematic.  See my blog

Don't USE INDEX or FORCE INDEX; it may help today, but it is likely to hurt tomorrow.

It is rarely useful to start with a column that will be used in a 'range':  KEY indx_posted_on (posted_on,source_id)

For that query: INDEX(type, source_id, posted_on),INDEX(type, posted_on).  The first of those covers the case where you have only onesource_id.  (Does such happen?).  The second one is better thanINDEX(posted_on)`.

More on optimal indexes  To summarize:  =s first, optionally INs, and one range last.  But the IN keeps the ORDER BY from being consumed.

Also, be sure that innodb_buffer_pool_size is about 70% of available RAM; this may help cut back on I/O.

UPDATE (based on OP's UPDATE):

INDEX(source_id, posted_on) -- IN + range -- should help with WHERE
INDEX(posted_on) -- part of WHERE, plus may consume ORDER BY


The Optimizer will pick between the two.

If AND metrics_1 BETWEEN... is added in, the performance will be worse.  So, keep it out of the query if the user does not need it.

Since you have a large set of possible queries, I recommend building a few indexes:

INDEX(posted_on) -- to handle the ORDER BY, in case nothing else works well
INDEX(const, posted_on) -- to partially handle WHERE, plus ORDER BY
INDEX(const, range) -- a few 2-column indexes;
          first is something used with '=', second is BETWEEN (or other range)
INDEX(in, range) -- a few more 2-column indexes;
          first is something used with 'IN', second is a range


Aim for no more than 10 total.  Watch what users usually ask about as a guide to which 'const'/'in'/'range' columns to index.  And/or look at what queries are "too" slow.

Don't say filter.* unless you really need all the columns.  In particular, if all TEXT columns can be omitted, there may be a performance gain in how temp tables are handled.

For large tables with text columns, this "lazy eval" may help performance:

SELECT filter.*
    FROM (
        SELECT primary-key-fields-of-filter
                  rest-of-existing-SELECT
                  order-by-limit
         ) x
    JOIN filter USING(primary-key-fields-of-filter)
    ORDER BY repeat-the-order-by-but-not-limit