Bad SQL statement causes Lists and Segments not shown

When you have a larger number of subscribers / segments, you will not see the lists or segments because the webserver will timeout on PHP-FPM. The statement in the slow query log looks like this:

SELECT
t0_.wp_user_id AS wp_user_id_0,
t0_.is_woocommerce_user AS is_woocommerce_user_1,
t0_.first_name AS first_name_2,
t0_.last_name AS last_name_3,
t0_.email AS email_4,
t0_.status AS status_5,
t0_.subscribed_ip AS subscribed_ip_6,
t0_.confirmed_ip AS confirmed_ip_7,
t0_.confirmed_at AS confirmed_at_8,
t0_.last_subscribed_at AS last_subscribed_at_9,
t0_.unconfirmed_data AS unconfirmed_data_10,
t0_.source AS source_11,
t0_.count_confirmations AS count_confirmations_12,
t0_.unsubscribe_token AS unsubscribe_token_13,
t0_.link_token AS link_token_14,
t0_.id AS id_15,
t0_.created_at AS created_at_16,
t0_.updated_at AS updated_at_17,
t0_.deleted_at AS deleted_at_18

FROM teo_mailpoet_subscribers t0_

INNER JOIN teo_mailpoet_subscriber_segment t1_

INNER JOIN teo_mailpoet_segments t2_

WHERE t2_.type = ‘woocommerce_users’ AND t0_.is_woocommerce_user = 1 LIMIT 1;

—

There is no ON clause (<join specification>), so: A join without condition is a cross join. A cross join repeats each row for the left hand table for each row in the right hand table – and we end up Rows_examined: 1.222.903.279 (1.2 million).

This query is like seriously WRONG, apart from the fact the it should never be run in our case as we have no WooCommerce installed.

Problem temporarily resolved by editing /lib/Segments/WooCommerce.php:

public function shouldShowWooCommerceSegment() {
return false; // TR

The actual problem is probably in the function called 2 lines below that: $woocommerceUserExists = $this->subscribersRepository->woocommerceUserExists();

Tim Reeves