How to maximize the relationship of two tables through the third?

Question

MySQL

Essence: there is a plate with applications, a plate with sources and a connecting plate, applications can have many sources, but in this case, you need to combine an application only with the first source.
Sketched a demo - sqlfiddle.com/#!9/53d445/1 The
query from the demo works well when there is little data, but when there are millions of rows to process, the query takes a very long time.
Question: how it is possible to optimize the given request?
UPD: additional filters will be applied to the request (by date, source, other data)
UPD2: in the selection, most often there will be a count by the total number of rows

Answer 1

Create indexes?
Or at least mark the columns as foreign keys.
Alternatively, you can try changing the main table:

SELECT
r.id as req,
ss.id as ss_id,
ss.user_id as user,
min(ss.created_at) AS date
FROM seo_source AS ss
INNER JOIN seo_user_request AS su ON su.user_id = ss.user_id
INNER JOIN request as r ON r.id = su.request_id
GROUP BY ss.id

Answer 2

I see it like this - select must be performed on the seo_source table with the same group and sort, inner join on the user, left join on request.
You have two requests in one, and so one. Will work faster.

Answer 3

Try like this:

select `r`.`id` as `req`, `ss`.`user_id` as `user`, `ss`.`created_at` as `date`
  from (
    select `user_id`, min(`created_at`) as `min_date` 
      from `seo_source` 
      group by `user_id`
  ) as ss
  right join `seo_user_request` as `su` on `su`.`user_id` = `ss`.`user_id`
  left join `request` as `r` on `r`.`id` = `su`.`request_id`

Selecting `ss`.`id` does not make sense, since the grouping will take the value from an arbitrary string.