WP_Query times out

What you are seeing isn’t “bad” exactly, you’ve just got a terrifically labor intensive query and WP_Query is not smart enough to optimize it– it doesn’t even try. Briefly, you’ve got a bunch of JOINs, you’ve got two LIKE conditions, which will read every row in the table of that post type, those likes are in a OR relationship which doubles the work, and you have an bunch of comparison– <=— operators which will also read every row in the table. All of this, as you’ve seen, gets worse with every condition you add. You are also ORDERing by two values, which is also pretty labor intensive. The database engine (not WP_Query) will make every attempt to optimize the query but it is a hard, hard query to optimize and your database server can’t handle the strain.

You are going to have to try to help things along.

I notice several lines like:

AND ( mt2.meta_key = 'single_rooms'
    AND Cast(mt2.meta_value AS signed) >= '3453' )
AND ( mt3.meta_key = 'single_rooms'
    AND Cast(mt3.meta_value AS signed) <= '345' )

You don’t post your query arguments but I am guessing you have multiple meta_querys something like this:

'meta_query' => array(
    array(
        'key'     => 'single_room',
        'value'   => '3435',
        'compare' => '>=',
    ),
),

Instead of one like this:

'meta_query' => array(
    array(
        'key'     => 'single_room',
        'value'   => array(3435,345),
        'compare' => 'BETWEEN',
    ),
),

That might cut down on the JOINs and speed things up, assuming WP_Query doesn’t convert that BETWEEN (which is a valid MySQL query condition).

Don’t order the query. Do that later in PHP. You need a filter, I believe, to completely remove ordering but at least don’t add any more work with your arguments.

You can prevent SQL_CALC_FOUND_ROWS by adding 'no_found_rows' => true to your arguments. That can improve performance but might mess up some Loop functions so test it and pay attention.

If that is not enough, query the postmeta table separately for post IDs and pass those in via the post__in argument. You will have to write your own SQL. There in not Core function that I am aware of that will query post meta in the way you will need (It is a failure in Core that I complain about periodically)

Beyond that, optimizing is going to mean some very complicated SQL.

One final note: Learn to love EXPLAIN.