Newsletter\Model\Subscriber::loadByEmail() does not use MySQL index

[schmengler]

Preconditions

1. Any Magento version between 1.0 and 2.2

Steps to reproduce

1. Call $subscriber->loadByEmail($email) with debugger or profiler
2. Inspect SQL query
3. Inspect execution plan EXPLAIN SELECT * FROM newsletter_subscriber WHERE subscriber_email='...'

Expected result

1. An index should be use to retrieve the subscriber instantly

Actual result

1. No index is used, resulting in a slow "Using where" query like this:

+----+-------------+-----------------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table                 | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-----------------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | newsletter_subscriber | ALL  | NULL          | NULL | NULL    | NULL | 75735 | Using where |
+----+-------------+-----------------------+------+---------------+------+---------+------+-------+-------------+

[devamitbera]

#mm18in

[magento-engcom-team]

@devamitbera thank you for joining. Please accept team invitation here and self-assign the issue.

[ishakhsuvarov]

Hi @schmengler. Thank you for your report.
The issue has been fixed in #13033 by @devamitbera in 2.2-develop branch
Related commit(s):

• 7559783
• b853497
• be8b258

The fix will be available with the upcoming patch release.

[magento-engcom-team]

Hi @schmengler. Thank you for your report.
The issue has been fixed in magento-engcom/magento2ce#1351 by @magento-engcom-team in 2.3-develop branch
Related commit(s):

• 70dd5a3
• 54bc3a1
• 3debd3f
• 72c4965
• e60ecdf
• 8703b3d
• bd88ed8
• 1c70b18
• 5fd7481
• 7c61506
• 2653b8a
• 917a03f
• eca73d0
• 26f3109
• c223484
• 3b90ad7
• ad317c2
• 53e721c
• 6114120
• 63b4839

The fix will be available with the upcoming 2.3.0 release.