SQL query to retrieve the number of WordPress posts with all given categories

There is a working example that only counts posts that are associated with both categories. This is tested with 3 posts, not 5000.

function count_posts_with_categories($cat_ids) {

    global $wpdb;
    $cat_count = 0;

    // A subquery to get all posts that are assigned to each category
    $subquery = "SELECT object_id FROM $wpdb->term_relationships WHERE term_taxonomy_id IN (" . implode(',', $cat_ids) . ") GROUP BY object_id HAVING COUNT(DISTINCT term_taxonomy_id) = " . count($cat_ids);

    // Use the subquery to count the number of posts that are assigned to both categories
    $cat_count = $wpdb->get_var(
        "SELECT COUNT(*) 
         FROM $wpdb->posts 
         WHERE post_status="publish" 
         AND post_type="post" 
         AND ID IN ($subquery)"
    );

    return $cat_count;
}

$cat_array = array(1, 2); // Replace with actual category IDs
$count = count_posts_with_categories($cat_array);
echo "Number of posts: " . $count;