forum.lissyara.su

Код: Выделить всё

mysql> create view v_client_portfolio_high
    -> (
    ->     client_id,
    ->     client_first_name,
    ->     client_last_name,
    ->     portfolio_value
    -> )
    -> as
    -> select  b.client_id,
    ->  b.client_first_name,
    ->  b.client_last_name,
    ->  sum(number_of_units * price)  -
    ->  case
    ->          (select sum(number_of_units * price)
    ->          from client_transaction d
    ->          where d.action = 'sell' and
    ->          d.client_id = b.client_id)
    ->  when NULL then 0
    ->  else
    ->          (select sum(number_of_units * price)
    ->          from client_transaction d
    ->          where d.action = 'sell' and
    ->          d.client_id = b.client_id)
    ->  end portfolio_value
    -> from    client_transaction a,
    ->  client b
    -> where   a.client_id = b.client_id and
    ->  action = 'buy'
    -> group    by b.client_id,
    ->  b.client_first_name,
    ->  b.client_last_name
    -> having   portfolio_value > 1000000;
Query OK, 0 rows affected (0.00 sec)

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v_client_portfolio_high;
+-----------+-------------------+------------------+-----------------+
| client_id | client_first_name | client_last_name | portfolio_value |
+-----------+-------------------+------------------+-----------------+
|         5 | ABNER             | ROSSELLETT       |      1252115.50 |
|       500 | CANDICE           | BARTLETT         |      1384877.50 |
+-----------+-------------------+------------------+-----------------+
2 rows in set (0.47 sec)

mysql> set profiling=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration   | Query                                         |
+----------+------------+-----------------------------------------------+
|        0 | 0.00007300 | set profiling=1                               |
|        1 | 0.00044700 | select count(*) from client where broker_id=2 |
|        2 | 0.00003400 | set profiling=0                               |
|        3 | 0.00007400 | set profiling=1                               |
|        4 | 0.63789700 | alter table t engine=myisam                   |
|        5 | 0.00004000 | set profiling=0                               |
|        6 | 0.00007600 | set profiling=1                               |
|        7 | 4.01965600 | select * from v_client_portfolio_high         |
|        8 | 0.00003500 | set profiling=0                               |
|        9 | 0.00007500 | set profiling=1                               |
|       10 | 0.45292700 | select * from v_client_portfolio_high         |
|       11 | 0.00003800 | set profiling=0                               |
+----------+------------+-----------------------------------------------+
12 rows in set (0.00 sec)

mysql> select min(seq) seq,state,count(*) numb_ops,
    -> round(sum(duration),5) sum_dur, round(avg(duration),5) avg_dur,
    -> round(sum(cpu_user),5) sum_cpu, round(avg(cpu_user),5) avg_cpu
    -> from information_schema.profiling
    -> where query_id = 10
    -> group by state
    -> order by seq;
+-------+----------------------+----------+---------+---------+---------+---------+
| seq   | state                | numb_ops | sum_dur | avg_dur | sum_cpu | avg_cpu |
+-------+----------------------+----------+---------+---------+---------+---------+
|     0 | (initialization)     |        1 | 0.00004 | 0.00004 | 0.00000 | 0.00000 |
|     1 | Opening tables       |        1 | 0.00024 | 0.00024 | 0.00000 | 0.00000 |
|     2 | System lock          |        1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |
|     3 | Table lock           |        1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |
|     4 | checking permissions |        1 | 0.00012 | 0.00012 | 0.00100 | 0.00100 |
|     5 | optimizing           |        4 | 0.00005 | 0.00001 | 0.00000 | 0.00000 |
|     6 | statistics           |        4 | 0.00007 | 0.00002 | 0.00000 | 0.00000 |
|     7 | preparing            |        4 | 0.00005 | 0.00001 | 0.00000 | 0.00000 |
|     8 | Creating tmp table   |        1 | 0.00003 | 0.00003 | 0.00000 | 0.00000 |
|     9 | executing            |    11194 | 0.04983 | 0.00000 | 0.01800 | 0.00000 |
|    10 | Copying to tmp table |        1 | 0.00008 | 0.00008 | 0.00000 | 0.00000 |
|    15 | Sending data         |    11195 | 0.39853 | 0.00004 | 0.36794 | 0.00003 |
| 22401 | Sorting result       |        1 | 0.00375 | 0.00375 | 0.00400 | 0.00400 |
| 22403 | removing tmp table   |        2 | 0.00005 | 0.00002 | 0.00000 | 0.00000 |
| 22405 | init                 |        1 | 0.00002 | 0.00002 | 0.00000 | 0.00000 |
| 22411 | end                  |        1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |
| 22412 | query end            |        1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 |
| 22413 | freeing items        |        1 | 0.00002 | 0.00002 | 0.00000 | 0.00000 |
| 22414 | closing tables       |        2 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |
| 22417 | logging slow query   |        1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 |
+-------+----------------------+----------+---------+---------+---------+---------+
20 rows in set (0.44 sec)