MySQL: need help making a query faster
When you have a query that has multiple criteria on the same table a compound index is needed. MySQL will only use a single index for a table (or alias) so make sure its the right one.

First look at discovery since its on the left of the join. Start with items referenced and then ranges. So:


provider
event_datetime


Elements like resource because of the LIKE %x% expression can't use and index. For a small table we relay that (provider,event_datetime) restruct the table small enough to be be significant about including resource in the index.

So:

CREATE INDEX provider_event_datetime ON discovery (provider,event_datetime);


Note: is whitelisted.provider need to be the same as the discovery_provider?

A limit on the inner query is quite unpredictable without an ORDER BY.

The remaining join on logs is looking it up by the PK. No additional indexing is required there.

Look at EXPLAIN {query} to examine what indexes it is using and how.