我有一个基本表（id表）和多个表（数据表），这些表具有与id相关的数据，每个表具有不同的类型数据。我想从这些不同的表中按多种条件过滤ID。
我按如下方式创建了查询，但这太慢了（大约3分钟）。我检查了解释信息，但仍然找不到更好的方法。有人能找到更快的方法吗？
询问
select jj.id, jj.imgtitle, jj.alias
from jjtable jj
inner join jjtable_catg jjc on jj.catid = jjc.cid
left join jjtable_map jjtm_name on jj.id = jjtm_name.picid
left join jjtable_tags jjts_name on jjtm_name.tid = jjts_name.tid
left join jjtable_map_ge jjtm_ge on jj.id = jjtm_ge.picid
left join jjtable_tags jjts_ge on jjtm_ge.tid = jjts_ge.tid
left join jjtable_map_ban jjtm_ban on jj.id = jjtm_ban.picid
left join jjtable_tags jjts_ban on jjtm_ban.tid = jjts_ban.tid
left join jjtable_map_per jjtm_per on jj.id = jjtm_per.picid
left join jjtable_tags jjts_per on jjtm_per.tid = jjts_per.tid
left join jjtable_map_fea jjtm_fea on jj.id = jjtm_fea.picid
left join jjtable_tags jjts_fea on jjtm_fea.tid = jjts_fea.tid
left join jjtable_map_ev jjtm_ev on jj.id = jjtm_ev.picid
left join jjtable_tags jjts_ev on jjtm_ev.tid = jjts_ev.tid
left join jjtable_map_ag jjtm_ag on jj.id = jjtm_ag.picid
left join jjtable_tags jjts_ag on jjtm_ag.tid = jjts_ag.tid
left join jjtable_map_fa jjtm_fa on jj.id = jjtm_fa.picid
left join jjtable_tags jjts_fa on jjtm_fa.tid = jjts_fa.tid
left join jjtable_map_im jjtm_im on jj.id = jjtm_im.picid
left join jjtable_tags jjts_im on jjtm_im.tid = jjts_im.tid where jj.published = 1
and jj.approved = 1
and jjts_fea.tid in(87,90)
and jjts_fea.delete_flag = 0
and jjtm_fea.delete_flag = 0
and jjc.cid in(4,10)
and jjts_name.tid in(77)
and jjts_name.delete_flag = 0
and jjtm_name.delete_flag = 0
and jjts_per.tid in(28,36)
and jjts_per.delete_flag = 0
and jjtm_per.delete_flag = 0
and jjts_ag.tid in(98,99)
and jjts_ag.delete_flag = 0
and jjtm_ag.delete_flag = 0
and jjts_fa.tid in(104,107)
and jjts_fa.delete_flag = 0
and jjtm_fa.delete_flag = 0
group by jj.id
order by case when date(jj.date) > date_add(date(now()), interval -14 day) then jj.view end DESC, jj.id DESC
编辑：谢谢您的评论。首先，我添加信息作为您的请求。
为什么需要此查询；
该查询旨在从“表单输入”标签接收过帐数据，用户可以从不同类别中选择选项来过滤ID。这就是为什么此查询包含此示例中未使用的表和数据的原因。
左联接如此之多的原因是，每个“映射”表都有类别映射信息以过滤ID。等等，用户希望找到与“ fea”，87、90和“ ag”，98、99映射的ID。
说明选择；
+----+-------------+--------------+--------+-------------------+-----------------+---------+------------------------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+--------+-------------------+-----------------+---------+------------------------------------------------+------+---------------------------------+
| 1 | SIMPLE | jjts_name | const | PRIMARY | PRIMARY | 4 | const | 1 | Using temporary; Using filesort |
| 1 | SIMPLE | jjc | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
| 1 | SIMPLE | jjts_pe | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where; Using join buffer |
| 1 | SIMPLE | jjts_fea | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where; Using join buffer |
| 1 | SIMPLE | jjts_ag | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where; Using join buffer |
| 1 | SIMPLE | jjts_fa | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where; Using join buffer |
| 1 | SIMPLE | jj | ref | PRIMARY,idx_catid | idx_catid | 4 | jjc.cid | 95 | Using where |
| 1 | SIMPLE | jjtm_ag | eq_ref | udx_picid_tid | udx_picid_tid | 8 | jj.id,jjts_ag.tid | 1 | Using where |
| 1 | SIMPLE | jjtm_fa | eq_ref | udx_picid_tid | udx_picid_tid | 8 | jj.id,jjts_fa.tid | 1 | Using where |
| 1 | SIMPLE | jjtm_fea | eq_ref | udx_picid_tid | udx_picid_tid | 8 | jj.id,jjts_fea.tid | 1 | Using where |
| 1 | SIMPLE | jjtm_pe | eq_ref | udx_picid_tid | udx_picid_tid | 8 | jjtm_fea.picid,jjts_per.tid | 1 | Using where |
| 1 | SIMPLE | jjtm_ev | ref | udx_picid_tid | udx_picid_tid | 4 | jjtm_fa.picid | 3 | Using index |
| 1 | SIMPLE | jjts_ev | eq_ref | PRIMARY | PRIMARY | 4 | jjtm_ev.tid | 1 | Using index |
| 1 | SIMPLE | jjtm_im | ref | udx_picid_tid | udx_picid_tid | 4 | jjtm_pe.picid | 6 | Using index |
| 1 | SIMPLE | jjts_im | eq_ref | PRIMARY | PRIMARY | 4 | jjtm_im.tid | 1 | Using index |
| 1 | SIMPLE | jjtm_ge | ref | udx_picid_tid | udx_picid_tid | 4 | jj.id | 23 | Using index |
| 1 | SIMPLE | jjts_ge | eq_ref | PRIMARY | PRIMARY | 4 | jjtm_ge.tid | 1 | Using index |
| 1 | SIMPLE | jjtm_ban | ref | udx_picid_tid | udx_picid_tid | 4 | jjtm_pe.picid | 9 | Using index |
| 1 | SIMPLE | jjts_ban | eq_ref | PRIMARY | PRIMARY | 4 | jjtm_ban.tid | 1 | Using index |
| 1 | SIMPLE | jjtm_name | eq_ref | udx_picid_tid | udx_picid_tid | 8 | jjtm_fea.picid,const | 1 | Using where |
+----+-------------+--------------+--------+-------------------+-----------------+---------+------------------------------------------------+------+---------------------------------+
20 rows in set (2 min 15.87 sec)
显示列；
mysql> show columns from jjtables;
+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| catid | int(11) | NO | MUL | 0 | |
| imgtitle | text | NO | | NULL | |
| alias | varchar(255) | NO | | | |
| date | datetime | NO | | NULL | |
| view | int(11) | NO | | 0 | |
| published | tinyint(1) | NO | | 0 | |
| approved | tinyint(1) | NO | | 0 | |
+--------------+------------------+------+-----+---------+----------------+
mysql> show columns from jjtable_catg;
+--------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+----------------+
| cid | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | | |
| alias | varchar(255) | NO | | | |
| parent | int(11) | NO | MUL | 0 | |
| desc | text | YES | | NULL | |
| order | int(11) | NO | | 0 | |
+--------------+---------------------+------+-----+---------+----------------+
mysql> show columns from jjtable_tags;
+--------------+-------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------------------+----------------+
| tid | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(60) | NO | UNI | NULL | |
| talias | varchar(60) | NO | | | |
| ttypeid | int(11) | NO | | 1 | |
| pa_tid | int(11) | NO | | 0 | |
| delete_flag | tinyint(1) | NO | | 0 | |
| created | datetime | NO | | 0000-00-00 00:00:00 | |
| created_by | int(11) | NO | | 0 | |
| modified | datetime | NO | | 0000-00-00 00:00:00 | |
| modified_by | int(11) | NO | | 0 | |
| t_due | datetime | NO | | 0000-00-00 00:00:00 | |
+--------------+-------------+------+-----+---------------------+----------------+
mysql> show columns from jjtable_map;
+-------------+------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| picid | int(11) | NO | MUL | NULL | |
| tid | int(11) | NO | | NULL | |
| delete_flag | tinyint(1) | NO | | 0 | |
| created | datetime | NO | | 0000-00-00 00:00:00 | |
| created_by | int(11) | NO | | 0 | |
| modified | datetime | NO | | 0000-00-00 00:00:00 | |
| modified_by | int(11) | NO | | 0 | |
+-------------+------------+------+-----+---------------------+----------------+
对不起，我的解释不好。希望此编辑可以帮助您更好地理解。编辑结束
尝试以下操作：将您的左联接转换为一堆WHERE EXISTS子查询，然后删除GROUP BY，因为（这是我的怀疑），这是您真正要表达的意思。
select
jj.id, jj.imgtitle, jj.alias
from
jjtable jj
-- you could drop this join, you don't do anything with jjtable_catg
inner join jjtable_catg jjc on jjc.cid = jj.catid
where
jj.published = 1
and jj.approved = 1
and jjc.cid in(4, 10)
and exists (
select 1
from jjtable_map m inner join jjtable_tags t on m.tid = t.tid
where m.picid = jj.id m.delete_flag = 0 and t.tid in (77) and t.delete_flag = 0
)
and exists (
select 1
from jjtable_map_per m inner join jjtable_tags t on m.tid = t.tid
where m.picid = jj.id m.delete_flag = 0 and t.tid in (28,36) and t.delete_flag = 0
)
and exists (
select 1
from jjtable_map_fea m inner join jjtable_tags t on m.tid = t.tid
where m.picid = jj.id m.delete_flag = 0 and t.tid in (87,90) and t.delete_flag = 0
)
and exists (
select 1
from jjtable_map_ag m inner join jjtable_tags t on m.tid = t.tid
where m.picid = jj.id m.delete_flag = 0 and t.tid in (98,99) and t.delete_flag = 0
)
and exists (
select 1
from jjtable_map_fa m inner join jjtable_tags t on m.tid = t.tid
where m.picid = jj.id m.delete_flag = 0 and t.tid in (104,107) and t.delete_flag = 0
)
order by
case when date(jj.date) > date_add(date(now()), interval -14 day) then jj.view end DESC,
jj.id DESC
同时创建这些复合索引（如果缺少）：
jjtable_catg： (cid)jjtable_tags： (tid, delete_flag)jjtable_map和所有jjtable_map_*：(picid, delete_flag, tid)本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句