How to optimize the query? What i could to read, for understand it? (in russian if you can, or english)

Question

Now my MYSQL query is below.

But in 700 projects, 50 params and 12000 projectparams. foreign keys created, but what indexes to create - i dont know :( the query takes 45+ seconds. It is too big, as i read on some forums, even if i want make it in CRON every 5 minutes.

How to optimize that query? and what indexes i want to create? and how to understand that process? Thanks.

  SELECT
        `tt1`.`id` `projects_id`,
        `tt2`.`projectparams_id`,
        `tt2`.`defaultvalue`,
        `tt2`.`defaultvaluealias`,
        `tt2`.`globalvalue`,
        `tt2`.`globalvaluealias`,
        `tt2`.`value`,
        `tt2`.`valuealias`
    FROM
        `projects` `tt1`
    LEFT JOIN
    (
        SELECT
            `t1`.`id` `projectparams_id`,
            `t1`.`defaultvalue`,
            `t1`.`defaultvaluealias`,
            `t1`.`globalvalue`,
            `t1`.`globalvaluealias`,
            `t2`.`value`,
            `t2`.`valuealias`,
            `t2`.`projects_id`
        FROM
            `projectparams` `t1`
        LEFT JOIN `projects_projectparams` `t2` ON `t2`.`projectparams_id` = `t1`.`id`
    ) tt2 ON `tt1`.`id` = `tt2`.`projects_id`

Answer 1

In your comments to PaulF's answer it shows you were not only looking for a query optimization, but for another query really. You don't want all products with or without specific parameters, but all possible combinations of products and parameters.

You should have made this a new request.

Here is the query to get all project / parameter combinations along with the actual project parameters:

select
  p.id as projects_id,
  pp.id projectparams_id,
  pp.defaultvalue,
  pp.defaultvaluealias,
  pp.globalvalue,
  pp.globalvaluealias,
  ppp.value,
  ppp.valuealias
from projects p
cross join projectparams pp 
left join projects_projectparams ppp on ppp.projects_id = p.id 
                                     and ppp.projectparams_id = pp.id;