SQL query [MySQL] optimization?

Question

MySQL

Hello everyone, I probably have two questions

1. Where can I read about how queries are actually executed in Muscle? I'm talking about understanding which places are bottlenecks, I want to understand in what order and according to what rules everything happens

. For example, if I have a request of the form and then there is a join with 10! tables and a big where at the end with a grouping and an order, and the exploit shows that and then 10 more lines. in the next 3 links of the eq_ref type, and in the remaining refs from the manual it says that the combination of strings in my case is 117 tons of strings? or something else? Is the nested select executed every time for every row? or not? How to understand “because of what” such a slow selection occurs in the query?

SELECT DISTINCT s.c_id as c_id ,( SELECT IF (s0.period_id=1, s0.sum*180, IF (s0.period_id=2, s0.sum*4.5, IF (s0.period_id=3, s0.sum, IF (s0.period_id=4, s0.sum/12, 0) ) ) ) as monthly ...




id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY s ALL c_id,sum_idx NULL NULL NULL 117108 Using where; Using temporary; Using filesort
...



eq_ref
Для каждой комбинации строк из предыдущих таблиц будет cчитываться одна строка из этой таблицы



Sorry I can't post the whole request :(

Answer 1

Try to use the profiler tool, the article was even here:
habrahabr.ru/blogs/mysql/70435/ I
also suspect from experience that a lot of time can be spent on group and order.

Answer 2

Regarding optimization, I advise you to read (and learn) the reference manual - www.codenet.ru/db/mysql5/manual.ru_MySQL_Optimization.php

Answer 3

write before the EXPLAIN query, don't you?