Come to think of it I don't think we'll need an index on event_page_id. The query would get the notifications for the given user, join against the event table, then filter out the ones with the wrong page IDs; there's no opportunity to use an index for this unless we denormalize the page ID into the notifications table.

Change 293142 had a related patch set uploaded (by Catrope):
ApiEchoNotificatons: Add nottitles parameter

https://gerrit.wikimedia.org/r/293142

Change 293142 merged by jenkins-bot:
ApiEchoNotificatons: Add nottitles parameter

https://gerrit.wikimedia.org/r/293142

@Catrope - just double-checking with you that all notifications in db will be correctly accounted for.

1. Is it ok to have many rows with event_page_id = NULL?

MariaDB [enwiki]> select count(*) from echo_notification where notification_event in (select event_id from echo_event where event_page_id is NULL);
+----------+
| count(*) |
+----------+
|    10528 |
+----------+
1 row in set (0.32 sec)

2. Various events do not have event_page_id recorded

MariaDB [enwiki]> select distinct(event_type) from echo_event where event_page_id is NULL;

+---------------------------------+
| event_type                      |
+---------------------------------+
| cx-first-translation            |
| edit-user-talk                  |
| emailuser                       |
| ep-campus-add-notification      |
| ep-instructor-add-notification  |
| ep-online-add-notification      |
| ep-student-add-notification     |
| flow-mention                    |
| flow-post-edited                |
| flow-post-reply                 |
| flow-summary-edited             |
| flow-thank                      |
| flow-topic-renamed              |
| gather-hide                     |
| gettingstarted-continue-editing |
| gettingstarted-start-editing    |
| reverted                        |
| user-rights                     |
| welcome                         |
+---------------------------------+
19 rows in set (0.50 sec)

3. page_title is also missing in many records

MariaDB [enwiki]> select count(*) from echo_event where event_page_title is NULL;
+----------+
| count(*) |
+----------+
|   139535 |
+----------+
1 row in set (0.16 sec)

event_page_namespace and event_page_title are deprecated and will be removed, see T136427. Only event_page_id is used. That field is also blank for a few old (2013) notifications. For a few notification types, event_page_id is always NULL. They are welcome, emailuser and user-rights (until a few weeks ago user-rights notifications were associated with the main page, but that has now been fixed). Those are the only types you should see NULL page IDs for if you look at non-ancient notifications (say, 2014 and later).

Removing user-notice tag after Catrope said "Special:Notifications: that task has no user-facing impact, but it will power the filter-by-page part of the sidebar" on IRC.

In T137139#2388775, @Catrope wrote:

For a few notification types, event_page_id is always NULL. They are welcome, emailuser and user-rights (until a few weeks ago user-rights notifications were associated with the main page, but that has now been fixed). Those are the only types you should see NULL page IDs for if you look at non-ancient notifications (say, 2014 and later).

Other less known notification types may able have NULL in event_page_id (all cx- types, for instance).