左联盟是我想要的,但他们很慢？

概述:

我有三个表1)订阅者,bios和衬衫,我需要找到没有生物或衬衫的订户

桌子的布局如

用户

| season_id |  user_id |

生物

| bio_id | user_id |

衬衫尺码

| bio_id | shirtsize |

而且我需要找到所有没有生物或衬衫尺码的用户(如果没有生物;那么通过关系没有衬衫尺寸)对于任何给定的季节.

我最初写了一个像这样的查询:

SELECT *
   FROM subscribers s 
   LEFT JOIN bio b ON b.user_id = subscribers.user_id 
   LEFT JOIN shirtsizes ON shirtsize.bio_id = bio.bio_id 
WHERE s.season_id = 185181 AND (bio.bio_id IS NULL OR shirtsize.size IS NULL);

但现在需要10秒钟才能完成.

我想知道如何重构查询(或可能是问题),以便它可以合理地进行预编码.

这是mysql解释:( ogu = subscriber,b = bio,tn = shirtshize)

| id | select_type | table | type  | possible_keys | key     | key_len | ref         | rows   | Extra       |   
+----+-------------+-------+-------+---------------+---------+---------+-------------+--------+-------------+    
|  1 | SIMPLE      | ogu   | ref   | PRIMARY       | PRIMARY | 4       | const       |    133 | Using where |
|  1 | SIMPLE      | b     | index | NULL          | PRIMARY | 8       | NULL        | 187644 | Using index |
|  1 | SIMPLE      | tn    | ref   | nid           | nid     | 4       | waka2.b.nid |      1 | Using where | 

以上是相当消毒的,这是真实的信息:

mysql> DESCRIBE subscribers
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| subscribers  | int(11) | NO   | PRI |         |       | 
| uid       | int(11) | NO   | PRI |         |       | 


mysql> DESCRIBE bio;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| bio_id   | int(10) unsigned | NO   | PRI | 0       |       | 
| uid   | int(10) unsigned | NO   | PRI | 0       |       | 


mysql> DESCRIBE shirtsize;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| bio_id   | int(10) unsigned | NO   | PRI | 0       |       | 
| shirtsize   | int(10) unsigned | NO   | PRI | 0       |       | 

而真正的查询看起来像:

SELECT ogu.nid, ogu.is_active, ogu.uid, b.nid AS bio_node, tn.nid AS size
                  FROM og_uid ogu
                  LEFT JOIN bio b ON b.uid = ogu.uid
                  LEFT JOIN term_node tn ON tn.nid = b.nid
                  WHERE ogu.nid = 185033 AND ogu.is_admin = 0
                  AND (b.nid IS NULL OR tn.tid IS NULL)

nid是season_id或bio_id(带有类型); term_node将成为衬衫大小

查询应该没问题.我将通过查询分析器运行它并优化表上的索引.

联接是您可以在SQL查询上执行的最昂贵的操作之一.虽然它应该能够在某种程度上自动优化您的查询,但也可以尝试重组它.首先,我将代替SELECT*,确保指定您需要哪些列来自哪些关系.这会加快速度.

如果您只需要用户ID,例如:

SELECT s.user_id
   FROM subscribers s 
   LEFT JOIN bio b ON b.user_id = subscribers.user_id 
   LEFT JOIN shirtsizes ON shirtsize.bio_id = bio.bio_id 
WHERE s.season_id = 185181 AND (bio.bio_id IS NULL OR shirtsize.size IS NULL);

这将允许SQL数据库自己重新调整查询效率.