forum.lissyara.su

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

SELECT calls.number,count(*),sum(calls.duration),calls.way,phonebook.description from calls LEFT JOIN phonebook ON calls.number LIKE phonebook.number AND phonebook.login = 'atslog' OR calls.number LIKE phonebook.number AND phonebook.login IS NULL where (calls.timeofcall between '2011-04-01 00:00:00' AND '2011-04-30 23:59:59' AND calls.way='A' AND calls.number not in ( SELECT calls.number from calls where calls.timeofcall between '2011-04-01 00:00:00' AND '2011-04-30 23:59:59' AND calls.way='I') ) GROUP BY calls.number,calls.way,phonebook.description ORDER BY 1 DESC LIMIT 0,1000;

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

SELECT count(*) from calls where timeofcall between '2011-04-01 00:00:00' AND '2011-04-30 23:59:59';
+----------+
| count(*) |
+----------+
|    24393 |
+----------+
1 row in set (0.08 sec)

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

SELECT count(*) from calls where timeofcall between '2011-05-01 00:00:00' AND '2011-05-30 23:59:59';
+----------+
| count(*) |
+----------+
|    23542 |
+----------+
1 row in set (0.08 sec)

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

+----+--------------------+-----------+----------------+---------------+--------+---------+-------+------+----------------------------------------------+
| id | select_type        | table     | type           | possible_keys | key    | key_len | ref   | rows | Extra                                        |
+----+--------------------+-----------+----------------+---------------+--------+---------+-------+------+----------------------------------------------+
|  1 | PRIMARY            | phonebook | system         | login         | NULL   | NULL    | NULL  |    0 | const row not found                          |
|  1 | PRIMARY            | calls     | ref            | way           | way    | 4       | const | 8418 | Using where; Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | calls     | index_subquery | way,number    | number | 34      | func  |    2 | Using where                                  |
+----+--------------------+-----------+----------------+---------------+--------+---------+-------+------+----------------------------------------------+

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

+----+--------------------+-----------+--------+-----------------------+------------+---------+------------+------+---------------------+
| id | select_type        | table     | type   | possible_keys         | key        | key_len | ref        | rows | Extra               |
+----+--------------------+-----------+--------+-----------------------+------------+---------+------------+------+---------------------+
|  1 | PRIMARY            | phonebook | system | login                 | NULL       | NULL    | NULL       |    0 | const row not found |
|  1 | PRIMARY            | calls     | ref    | way,way_number        | way_number | 4       | const      | 6645 | Using where         |
|  2 | DEPENDENT SUBQUERY | calls     | ref    | way,number,way_number | way_number | 38      | const,func |    2 | Using where         |
+----+--------------------+-----------+--------+-----------------------+------------+---------+------------+------+---------------------+
3 rows in set (0.00 sec)