Does MySQL remove common subexpressions between SELECT clause and HAVING / GROUP BY

I often see people answer MySQL questions with these queries:

SELECT DAY(date), other columns
FROM table
GROUP BY DAY(date);

SELECT somecolumn, COUNT(*)
FROM table
HAVING COUNT(*) > 1;

I always like to alias a column and refer to it in a sentence, GROUP BYor HAVINGfor example

SELECT DAY(date) AS day, other columns
FROM table
GROUP BY day;

SELECT somecolumn, COUNT(*) AS c
FROM table
HAVING c > 1;

Is MySQL smart enough to notice that the expressions in subsequent sentences are the same as in SELECT, and do it only once? I'm not sure how to check this - it EXPLAINdoes not show any difference, but it does not seem to show how grouping or filtering does it in the first place; this is mainly useful for optimizing associations and sentences WHERE.

I tend to be pessimistic about optimizing MySQL, so I would like to provide all my help.

+4
2

, sleep(),
, : http://sqlfiddle.com/#!2/0bc1b/1

Select * FROM t;

| X |
|---|
| 1 |
| 2 |
| 2 |

SELECT x+sleep(1)
FROM t
GROUP BY x+sleep(1);

SELECT x+sleep(1) As name
FROM t
GROUP BY name;

3000 (3 ).
3 , 1 ,
, , .

+5

MySQL .

  • - ( ) . (: .)
  • - . Compiler, MySQL . : (a-b)*(a-b) .
  • - , . .
  • SQL- - ; . .
  • - . , .
  • VIEWs - . , a VIEW , SELECT. : UNION VIEW. , .

(: 100% - , , , MySQL 5.7, MariaDB 10.1 ..)

SELECT . , , , "" . : , . ...

NOW() . , . , , , NOW() . (SYSDATE() - .)

, only_full_group_by, GROUP BY , SELECT. , .

HAVING ORDER BY SELECT ( WHERE GROUP BY). , SELECT expr AS x ... HAVING expr, , expr, SELECT expr AS x ... HAVING x, , expr.

MariaDB 10.2 , / ; .

, - (DATE(date) COUNT(*)) . , , . , , .

+1

Source: https://habr.com/ru/post/1541734/


All Articles