I recently faced a really weird issue. I need to fetch data for event posts where the scenario is

• There are two meta keys start_date and expire_date.
• I need to show all the events where these posts expire_date value is greater than today's date and the events start_date should be less than or equal today's date. Pretty straight forward right?
• But here is a catch. I also need to add a query where the start_date id greater than today's date. This can be a OR condition where I need to show the events in both of these cases.

So normally what I did was to create a meta_query where the relation will be OR at first then for the expire_date and start_date condition an array with AND condition. In another array other condition for the greater start_date.
It looks like this:

'meta_query' => [
            'relation' => 'OR',
            [
                [
                    'key' => 'start_date',
                    'value' => date('Y-m-d'),
                    'compare' => '<=',
                    'type' => 'DATE'
                ],
                [
                    'key' => 'expire_date',
                    'value' => date('Y-m-d'),
                    'compare' => '>=',
                    'type' => 'DATE'
                ],
                'relation' => 'AND'
            ],
            [
                'key' => 'start_date',
                'value' => date('Y-m-d'),
                'compare' => '>',
                'type' => 'DATE'
            ]
        ];

This actually giving the correct result but it made the query complex. It made several inner joins and the condition was not efficient. So it was taking a lot of time to show the event results.
These are the joins made for this meta query:

INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
    INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
    INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
    INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id)
    INNER JOIN wp_postmeta AS mt4 ON (wp_posts.ID = mt4.post_id)

Here is the where condition only for this meta_query:

wp_postmeta.meta_key = 'start_date'
        AND((mt1.meta_key = 'expire_date')
        AND(((mt2.meta_key = 'start_date'
            AND CAST(mt2.meta_value AS DATE) <= '2023-02-03')
        AND(mt3.meta_key = 'expire_date'
            AND mt3.meta_value >= '1675350000'))
    OR(mt4.meta_key = 'start_date'
        AND CAST(mt4.meta_value AS DATE) > '2023-02-03')))

It was taking almost 30 seconds to 1 minute to fetch all the event datas and showing.
So I removed all the meta queries and replaced with the wpdb query to write custom queries.

From this above meta_query I replaced it. Queried to get the id's from two separate conditions for start_date and expire_date then merging these two arrays. Removed extra joins and too much complex meta_queries.

$ids1 = $wpdb->get_col("`QUERY for the expire and start date`");
$ids2 = $wpdb->get_col("`Query for start_date post ids`");

$ids = array_merge($ids1, $ids2);
in the wp args 'post__in' => $ids

Above wpdb queries made my query clear and I also understood what I was doing. It's performance improved. And I am now feeling relax! 😅
See you again! 🫶