How to speed up SQL query execution?

Question

SQL

There is a request that takes a very long time (more than 1 minute)

SELECT n.nomin as name , 
  (
    SELECT count(cl.id) 
    FROM `ci_clubs` as cl
    WHERE cl.id in 
    (
      SELECT cl2.id FROM `ci_clubs` as cl2
      LEFT OUTER JOIN `ci_contes` as con2 ON (con2.id_club=cl2.id)
      LEFT OUTER JOIN `ci_group_cont` as gc2 ON (gc2.contes_id = con2.id)
      LEFT OUTER JOIN `ci_nomin_groups` as ng2 ON (ng2.group_id = gc2.group_contes_id)
      WHERE ng2.nomin_id = n.id
    
    )
  ) as clubs,
  (
    SELECT count(con3.id) 
    FROM `ci_contes` as con3
    WHERE con3.id in 
    (
      SELECT con4.id FROM `ci_contes` as con4
      LEFT OUTER JOIN `ci_group_cont` as gc3 ON (gc3.contes_id = con4.id)
      LEFT OUTER JOIN `ci_nomin_groups` as ng3 ON (ng3.group_id = gc3.group_contes_id)
      WHERE ng3.nomin_id = n.id
    
    )
  ) as contes, (3) as tour_id
  
FROM `ci_contes` con
LEFT OUTER JOIN `ci_group_cont` as gc ON (gc.contes_id=con.id)
LEFT OUTER JOIN `ci_nomin_groups` AS ng ON (ng.group_id=gc.group_contes_id)
LEFT OUTER JOIN `ci_nomin` AS n ON (n.id = ng.nomin_id)
 
WHERE n.fest=3 AND con.id = 37 AND  n.id is not null

ORDER by n.id DESC

At the output I get the name of the nominations, the number of clubs, the number of participants
The database itself consists of:
- clubs
- participants (1 participant belongs to only one club)
- groups (from 1 to 41 participants in a group)
- nominations
Moreover, the same participant can be in any group
A, the same group can be in any nomination . In this case, the
connection is:
participant - group (multiple to multiple - done through an additional table)
group - nomination (multiple to many - done through an additional table)
number of participants - 900
nominations - 400
Total question: is it possible to somehow speed up this request?

Answer 1

For a start to esteem about indexes. Create them if necessary. (this is pretty easy)
Then read about explain (this is more difficult).

Answer 2

DO NOT SHOVEL IN IN Select and even with JOIN.
do two requests. cache, etc.

Answer 3

Imagine that in SELECT .., ( SELECT count() ... ) , ...
a subquery (inner select) is executed for each row in the outer selection. And you have two or even three of them, it will naturally slow down.
There are a lot of optimization options, you need to experiment:
1) splitting into simpler queries, assembling the result on the client
2) writing a stored procedure (this is not a good idea for MySQL) from simple queries and creating temporary tables

Answer 4

In theory it should be something like this:

SELECT n.nomin as name, count( DISTINCT cl.id ),  count( DISTINCT  con2.id )
  FROM `ci_nomin` AS n 
    LEFT OUTER  JOIN `ci_nomin_groups` as ng2 ON (  n.id = ng2.nomin_id )
    LEFT OUTER JOIN `ci_group_cont` as gc2 ON (  ng2.group_id = gc2.group_contes_id )
    LEFT OUTER JOIN `ci_contes` as con2 ON (  gc2.contes_id = con2.id )
    LEFT OUTER JOIN `ci_clubs` as cl2  ON (  con2.id_club = cl2.id )
GROUP BY n.nomin