php - mysql:优化此sql查询的最佳方法

Question

我想获得从该查询中获取结果的最佳方法

这是我的表格说明

学校

CREATE TABLE `schools` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `user` mediumint(5) NOT NULL,
 `gender` tinyint(1) NOT NULL,
 `time` int(11) NOT NULL,
 `status` tinyint(2) NOT NULL,
 `number` mediumint(6) NOT NULL,
 `name` varchar(75) NOT NULL,
 `address` varchar(75) NOT NULL,
 `admin` varchar(50) NOT NULL,
 `admin_phone` varchar(20) NOT NULL,
 `admin_email` varchar(30) NOT NULL,
 `school_phone` varchar(20) NOT NULL,
 `learn` tinyint(2) NOT NULL,
 `mr7la` tinyint(2) NOT NULL,
 `sfof` smallint(3) NOT NULL,
 `fswl` smallint(3) NOT NULL,
 `json` text NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `user` (`user`),
 KEY `status` (`status`),
 KEY `learn` (`learn`),
 KEY `mr7la` (`mr7la`),
 KEY `number` (`number`)
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=utf8

3agz

CREATE TABLE `3agz` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `school` int(11) NOT NULL,
 `tkss` int(11) NOT NULL,
 `teacher_7ess` int(11) NOT NULL,
 `teacher_master_7ess` int(11) NOT NULL,
 `time_added` int(11) NOT NULL,
 `reported` int(11) NOT NULL DEFAULT '0',
 `fixed` int(11) NOT NULL,
 `info` text NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `school` (`school`,`tkss`,`teacher_7ess`,`fixed`),
 KEY `school_2` (`school`),
 KEY `tkss` (`tkss`),
 KEY `reported` (`reported`),
 KEY `time_added` (`time_added`),
 KEY `school_3` (`school`,`time_added`),
 KEY `school_4` (`school`,`fixed`)
) ENGINE=InnoDB AUTO_INCREMENT=85 DEFAULT CHARSET=utf8

这是用于此目的的 SQL Fiddle

http://sqlfiddle.com/#!2/3313e0/4

<小时/>

你可以看到我使用的她的sql查询

SELECT
    schools.* , ( select count(id) from 3agz where 3agz.school = schools.id and fixed = 0 ) as has_3agz
           FROM
                schools
           WHERE
                ( select count(id) from 3agz where 3agz.school = schools.id and fixed = 0 ) > 0

limit 10

解释是

学校 => 小学:全部

3agz => 依赖子查询:引用

3agz => 依赖子查询:引用

我问我可以这样做吗？最好的方法是什么

1 - 我可以忽略在哪里的第二个子查询并根据选择中的第一个子查询获取它

2-如果第 1 个答案是你不能 我可以忽略第一个子查询 [ has_3agz 别名 ] 执行此查询后，我循环查找结果 [schools ids]

并像这样进行第二个查询

例如，第一个查询返回学校 ID 1 , 2 , 3 , 4

select school , count(id) from 3agz where school in ( 1 , 2 , 3 , 4 ) and fixed = 0

将每个计数附加到数组中的学校

希望你能理解我

致以诚挚的问候

Answer 1

SELECT schools.*, count(*) as has_3agz from schools
LEFT JOIN 3agz on 3agz.school = schools.id and fixed = 0
GROUP BY schools.id;