mysql - SQL 查询优化 join union distinct

Question

我有一个问题:

Select A.col1 as col1, B.col5 as col2, C.col10 as col3
    FROM Table A 
    JOIN Table B on(A.col2 = B.col2)
    JOIN table C on(C.col1 = B.col3)
UNION
SELECT A.col1 as col1, B.col5 as col2, NULL as col3
     FROM Table A 
     JOIN Table B on (A.col2 = B. col2) 
           where A.col4 != 'somevalue' 

有什么方法可以让它更快？？

Table A
--------
col1         col2          col4
gerry          1           'somevalue'
harry          2           'othervalue'
 tom           3           
sarah          4           'somevalue'

col2 of table A is the primary key

Table B
-------
col2         col3    col5
 1            45      34
 1            34      23
 1            56      67
 2            34      56
 Primary key of B is (col2, col3)
Table C
-------
col1     col10
 34       'heyya'
 467      'tyga'
 56       'pity'

C的主键也是复合的。其中一个键是 col1

 Output:
 col1     col2    col3
 gerry    23       'heyya'
 gerry    67       'pity'
 gerry    34        NULL
 harry     56      'heyya'

所以 B 的值在 C 中存在或在 A 中具有“somevalue”被调用。也称为具有两者的值。

Answer 1

这适合你吗？所以你得到所有行，如果 A.col4 <> 'somevalue' 那么你得到 NULL for col3

Select
      A.col1 as col1
    , B.col5 as col2
    , C.col10 as col3
    , if(A.col4 != 'somevalue', 1, 0) as col4
FROM Table A 
JOIN Table B on(A.col2 = B.col2)
    JOIN table C on(C.col1 = A.col3);