Доброго времени суток всем!
Есть вот такие таблицы:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
mysql> show create table assist;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| assist | CREATE TABLE `assist` (
`no` int(11) NOT NULL AUTO_INCREMENT,
`crt_date` datetime NOT NULL,
`close_date` datetime DEFAULT NULL,
`edit_date` datetime DEFAULT NULL,
`crt_user` int(11) NOT NULL,
`edit_user` int(11) NOT NULL,
`client_id` int(11) NOT NULL DEFAULT '1',
`country` int(6) NOT NULL,
`city` int(7) NOT NULL,
`ships_name` varchar(50) DEFAULT NULL,
`gender` int(1) DEFAULT NULL,
`name` tinytext NOT NULL,
`surname` tinytext NOT NULL,
`dob` date NOT NULL,
`caller` text,
`contact` varchar(50) NOT NULL,
`alter_contact` varchar(50) DEFAULT NULL,
`contact_email` varchar(50) DEFAULT NULL,
`complaint` varchar(500) NOT NULL,
`policy_no` tinytext NOT NULL,
`policy_start` date NOT NULL DEFAULT '0000-00-00',
`policy_expires` date NOT NULL DEFAULT '0000-00-00',
`policy_detail` text,
`prov1_code` int(11) NOT NULL DEFAULT '0',
`prov2_code` int(11) NOT NULL DEFAULT '0',
`prov3_code` int(11) NOT NULL DEFAULT '0',
`prov4_code` int(11) NOT NULL DEFAULT '0',
`prov5_code` int(11) NOT NULL DEFAULT '0',
`notes` text,
`status` int(2) NOT NULL,
`type` int(2) NOT NULL,
`active` int(1) NOT NULL DEFAULT '0',
`type_payment` int(1) DEFAULT NULL,
`preliminary` double(15,2) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `prov2_code` (`prov2_code`),
KEY `prov3_code` (`prov3_code`),
KEY `prov4_code` (`prov4_code`),
KEY `prov5_code` (`prov5_code`),
KEY `client_id` (`client_id`),
KEY `prov_code` (`prov1_code`,`prov2_code`,`prov3_code`,`prov4_code`,`prov5_code`),
KEY `country` (`country`),
KEY `crt_user` (`crt_user`),
KEY `crt_date` (`crt_date`),
KEY `prov1_code` (`prov1_code`),
FULLTEXT KEY `name` (`name`),
FULLTEXT KEY `surname` (`surname`),
FULLTEXT KEY `policy_no` (`policy_no`)
) ENGINE=MyISAM AUTO_INCREMENT=27875 DEFAULT CHARSET=utf8 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
mysql> show create table assistfin;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| assistfin | CREATE TABLE `assistfin` (
`reference_no` int(11) NOT NULL,
`no` decimal(20,2) NOT NULL,
`provider` int(6) NOT NULL,
`crt_user` int(3) NOT NULL,
`edit_user` int(3) DEFAULT NULL,
`bil_user` int(3) DEFAULT NULL,
`edit_date` datetime DEFAULT NULL,
`crt_date` datetime DEFAULT NULL,
`service_date` date DEFAULT NULL,
`tobilling_date` datetime DEFAULT NULL,
`payment_day_by_client` date DEFAULT NULL,
`dop_prov` date DEFAULT NULL,
`sent_to_client` date DEFAULT NULL,
`invoice_no` varchar(30) DEFAULT NULL,
`date_of_invoice` date NOT NULL,
`invoice_to_client` varchar(20) DEFAULT NULL,
`amount` double(15,4) DEFAULT NULL,
`amount_after` double(15,4) DEFAULT NULL,
`currency` int(11) DEFAULT NULL,
`amount_after_eur` double(15,4) DEFAULT NULL,
`amount_after_usd` double(15,4) DEFAULT NULL,
`amount_before_eur` double(15,4) NOT NULL,
`sav_eur` int(11) DEFAULT NULL,
`fee` int(11) DEFAULT NULL,
`fee_of_savings` int(11) DEFAULT NULL,
`paid_to_provider` decimal(15,4) DEFAULT NULL,
`status` int(11) NOT NULL,
`diagnosis` text,
`comments` varchar(10000) DEFAULT NULL,
`comments_to_eob` varchar(10000) DEFAULT NULL,
`comments_op` text,
`eob_no` int(11) NOT NULL DEFAULT '1',
`eob_ic_no` int(11) DEFAULT NULL,
`our_amount` double(15,4) DEFAULT NULL,
`our_currency` int(3) DEFAULT NULL,
`our_amount_eur` double(15,4) DEFAULT NULL,
`claimid` varchar(30) DEFAULT NULL,
`type` int(1) NOT NULL,
`pat_paid_loc` double(15,4) NOT NULL DEFAULT '0.0000',
`pat_paid_eur` double(15,4) DEFAULT '0.0000',
`amount_total_loc` double(15,4) DEFAULT NULL,
`amount_total_eur` double(15,4) DEFAULT NULL,
PRIMARY KEY (`no`),
UNIQUE KEY `tobilling_date` (`tobilling_date`),
KEY `crt_user` (`crt_user`),
KEY `crt_date` (`crt_date`),
KEY `reference_no` (`reference_no`),
KEY `bil_user` (`bil_user`),
FULLTEXT KEY `invoice_no` (`invoice_no`),
FULLTEXT KEY `invoice_to_client` (`invoice_to_client`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
mysql> show create table history;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| history | CREATE TABLE `history` (
`no` int(11) NOT NULL AUTO_INCREMENT,
`crt_date` datetime NOT NULL,
`crt_user` int(3) DEFAULT NULL,
`id` int(11) DEFAULT NULL,
`txt` mediumtext,
PRIMARY KEY (`no`),
UNIQUE KEY `combine` (`crt_date`,`crt_user`,`id`),
KEY `crt_date` (`crt_date`),
KEY `crt_user` (`crt_user`),
KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=411336 DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
mysql> show create table tasklist;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tasklist | CREATE TABLE `tasklist` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`no` int(11) NOT NULL,
`claim_id` decimal(20,2) DEFAULT NULL,
`crt_date` datetime NOT NULL,
`crt_ou` int(1) NOT NULL,
`deadline` datetime NOT NULL,
`complete_time` datetime DEFAULT NULL,
`task` text NOT NULL,
`comments_for_task` varchar(300) DEFAULT NULL,
`creator` int(3) NOT NULL,
`task_for` int(3) NOT NULL DEFAULT '1',
`performer` int(3) DEFAULT '1',
`complited` int(1) NOT NULL DEFAULT '2',
`view_count` int(3) NOT NULL DEFAULT '0',
`root` int(1) NOT NULL DEFAULT '1',
`parent_id` int(11) DEFAULT NULL,
`active_task` int(1) NOT NULL DEFAULT '0',
`auto_task_id` int(2) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `no` (`no`),
KEY `complited` (`complited`),
KEY `crt_date` (`crt_date`),
KEY `creator` (`creator`),
KEY `performer` (`performer`),
KEY `parent_id` (`parent_id`),
KEY `deadline` (`deadline`),
KEY `combine` (`creator`,`performer`,`no`),
KEY `crt_ou` (`crt_ou`)
) ENGINE=MyISAM AUTO_INCREMENT=215615 DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
mysql> show create table assist_mail;
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| assist_mail | CREATE TABLE `assist_mail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`case_id` int(11) NOT NULL,
`creator` int(3) NOT NULL,
`sender` text NOT NULL,
`recipient` text NOT NULL,
`date_send` datetime DEFAULT NULL,
`date_added` datetime NOT NULL,
`date_rec` datetime DEFAULT NULL,
`subject` text NOT NULL,
`mail_text` longtext NOT NULL,
`tb` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `combine1` (`creator`,`case_id`,`date_added`),
KEY `case_id` (`case_id`),
KEY `creator` (`creator`),
KEY `date_send` (`date_send`),
KEY `date_added` (`date_added`),
KEY `tb` (`tb`)
) ENGINE=MyISAM AUTO_INCREMENT=166256 DEFAULT CHARSET=utf8 |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
mysql> show create table users;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`login` varchar(20) NOT NULL,
`password` text,
`salt` text,
`lastlogin` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`show` int(1) NOT NULL DEFAULT '1',
`operations` int(1) NOT NULL DEFAULT '0',
`billing` int(1) NOT NULL DEFAULT '0',
`network` int(1) NOT NULL DEFAULT '0',
`doctor` int(1) NOT NULL DEFAULT '0',
`advanced` int(1) NOT NULL DEFAULT '0',
`oper_manager` int(1) NOT NULL DEFAULT '0',
`themes` int(3) NOT NULL DEFAULT '1',
`animation` int(1) NOT NULL DEFAULT '0',
`signature` varchar(2000) NOT NULL DEFAULT '<p>Global Solutions</p>',
`deleted` int(1) NOT NULL DEFAULT '0',
`block` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `operations` (`operations`),
KEY `network` (`network`),
KEY `billing` (`billing`),
KEY `operations_2` (`operations`),
FULLTEXT KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=176 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
mysql> show create table fileattach;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| fileattach | CREATE TABLE `fileattach` (
`no` int(11) NOT NULL AUTO_INCREMENT,
`case_no` int(11) DEFAULT NULL,
`creator` int(3) NOT NULL,
`date` datetime DEFAULT NULL,
`filename` tinytext NOT NULL,
`filesize` int(11) DEFAULT NULL,
`path` tinytext NOT NULL,
`gop_no` int(11) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `creator` (`creator`),
KEY `case_no` (`case_no`)
) ENGINE=MyISAM AUTO_INCREMENT=130776 DEFAULT CHARSET=utf8 |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Стоит такая задача, для каждого работающего пользователя (users.deleted=0) из таблицы users, считать количество созданного в каждой из таблиц за произвольный период.
Вот такой вот запрос создается на PHP:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
select users.id,users.name,users.operations,users.network,users.billing,users.lastlogin,users.advanced,users.oper_manager,
(SELECT COUNT(*) FROM assist where crt_user=users.id and 1 and assist.crt_date BETWEEN '2015-09-01' and '2015-09-23 23:59:59') as count,
(SELECT COUNT(*) FROM assistfin where crt_user=users.id and 1 and assistfin.crt_date BETWEEN '2015-09-01' and '2015-09-23 23:59:59') as count_billing,
(SELECT COUNT(*) FROM history where crt_user=users.id and 1 and history.crt_date BETWEEN '2015-09-01' and '2015-09-23 23:59:59') as history_count ,
(SELECT COUNT(*) FROM tasklist where creator=users.id and 1 and tasklist.crt_date BETWEEN '2015-09-01' and '2015-09-23 23:59:59') as task_count,
(SELECT COUNT(*) FROM tasklist where performer=users.id and 1 and tasklist.complete_time BETWEEN '2015-09-01' and '2015-09-23 23:59:59') as performer_count,
(SELECT COUNT(*) FROM fileattach where creator=users.id and gop_no is not NULL and 1 and fileattach.date BETWEEN '2015-09-01' and '2015-09-23 23:59:59' ) as gop_count,
(SELECT COUNT(*) FROM assist_mail where creator=users.id and tb=0 and assist_mail.date_send BETWEEN '2015-09-01' and '2015-09-23 23:59:59' ) as email_send,
(SELECT COUNT(*) FROM assist_mail where creator=users.id and tb=1 and assist_mail.date_added BETWEEN '2015-09-01' and '2015-09-23 23:59:59' ) as email_tb,
(SELECT COUNT(*) FROM assistfin where bil_user=users.id and 1 and assistfin.crt_date BETWEEN '2015-09-01' and '2015-09-23 23:59:59' ) as bil_count
FROM users
WHERE
users.show=1 and 1 and users.operations=1 and users.deleted=0 group by name ASC
EXPLAIN данного запроса:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
mysql> explain select users.id,users.name,users.operations,users.network,users.billing,users.lastlogin,users.advanced,users.oper_manager,(SELECT COUNT(*) FROM assist where crt_user=users.id and 1 and assist.crt_date BETWEEN '2015-09-01' and '2015-09-23 23:59:59') as count,(SELECT COUNT(*) FROM assistfin where crt_user=users.id and 1 and assistfin.crt_date BETWEEN '2015-09-01' and '2015-09-23 23:59:59') as count_billing, (SELECT COUNT(*) FROM history where crt_user=users.id and 1 and history.crt_date BETWEEN '2015-09-01' and '2015-09-23 23:59:59') as history_count ,(SELECT COUNT(*) FROM tasklist where creator=users.id and 1 and tasklist.crt_date BETWEEN '2015-09-01' and '2015-09-23 23:59:59') as task_count, (SELECT COUNT(*) FROM tasklist where performer=users.id and 1 and tasklist.complete_time BETWEEN '2015-09-01' and '2015-09-23 23:59:59') as performer_count, (SELECT COUNT(*) FROM fileattach where creator=users.id and gop_no is not NULL and 1 and fileattach.date BETWEEN '2015-09-01' and '2015-09-23 23:59:59' ) as gop_count, (SELECT COUNT(*) FROM assist_mail where creator=users.id and tb=0 and assist_mail.date_send BETWEEN '2015-09-01' and '2015-09-23 23:59:59' ) as email_send, (SELECT COUNT(*) FROM assist_mail where creator=users.id and tb=1 and assist_mail.date_added BETWEEN '2015-09-01' and '2015-09-23 23:59:59' ) as email_tb, (SELECT COUNT(*) FROM assistfin where bil_user=users.id and 1 and assistfin.crt_date BETWEEN '2015-09-01' and '2015-09-23 23:59:59' ) as bil_count from users where users.show=1 and 1 and users.operations=1 and users.deleted=0 group by name ASC;
+----+--------------------+-------------+------+--------------------------------+------------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------------+------+--------------------------------+------------+---------+-------+------+----------------------------------------------+
| 1 | PRIMARY | users | ref | operations,operations_2 | operations | 4 | const | 89 | Using where; Using temporary; Using filesort |
| 10 | DEPENDENT SUBQUERY | assistfin | ref | crt_date,bil_user | bil_user | 5 | func | 46 | Using where |
| 9 | DEPENDENT SUBQUERY | assist_mail | ref | combine1,creator,date_added,tb | combine1 | 4 | func | 1428 | Using where |
| 8 | DEPENDENT SUBQUERY | assist_mail | ref | combine1,creator,date_send,tb | combine1 | 4 | func | 1428 | Using where |
| 7 | DEPENDENT SUBQUERY | fileattach | ref | creator | creator | 4 | func | 1299 | Using where |
| 6 | DEPENDENT SUBQUERY | tasklist | ref | performer | performer | 5 | func | 2338 | Using where |
| 5 | DEPENDENT SUBQUERY | tasklist | ref | crt_date,creator,combine | combine | 4 | func | 1944 | Using where |
| 4 | DEPENDENT SUBQUERY | history | ref | combine,crt_date,crt_user | crt_user | 5 | func | 4408 | Using where |
| 3 | DEPENDENT SUBQUERY | assistfin | ref | crt_user,crt_date | crt_user | 4 | func | 484 | Using where |
| 2 | DEPENDENT SUBQUERY | assist | ref | crt_user,crt_date | crt_user | 4 | func | 400 | Using where |
+----+--------------------+-------------+------+--------------------------------+------------+---------+-------+------+----------------------------------------------+
10 rows in set (0.03 sec)
Проблема в том, что этот запрос отрабатывает около 2-3 минут и вешает всю базу, можно ли как-то его оптимизировать? |
