How can optimize this VIEW Statement?

I have a VIEW which is SLOW. I don't like the VIEW Statements as there are lot of JOINS and UNION开发者_C百科.

Here is the view statement.

Create VIEW NewView AS

SELECT t2.* FROM Table1 t1
JOIN Table2 t2
ON t1.Column1 = t2.Column1 AND t1.Column2 = t2.Column2
WHERE t1.Column3 !='String'

UNION

SELECT t1.*, 'Add this string to the Last Column' FROM Table1 t1
LEFT JOIN Table2 t2
ON t1.Column1 = t2.Column1 AND t1.Column2 = t2.Column2
WHERE t2.Column1 is null OR t1.Column3 ='String'
ORDER BY Column 4

Basically the idea is if a record exists in Table1 and Table2, the record from Table2 should overlay the record from Table1. How can I optimize this?

I have a primary key id INT NOT NULL AUTO_INCREMENT PRIMARY KEY in both the tables but I am not sure how I can integrate that with the view. I want the view to have a primary key or composite key. I cannot use other columns as all the columns can have null and duplicate values.

You can join and compare, using an outer join, then use COALESCE to prefer T2 to T1.

To retain a unique key, and assuming your ID's are all positive, you can make one table's id's negative.

SELECT
  COALESCE(t2.id,     -t1.id)      AS id,
  COALESCE(t2.Column1, t1.Column1) AS Column1,
  COALESCE(t2.Column2, t1.Column2) AS Column2
FROM
  Table1          AS t1
FULL OUTER JOIN
  Table2          AS t2
    ON  t1.Column1 = t2.Column2
    AND t1.Column2 = t2.Column2
WHERE
  COALESCE(t2.Column3, t1.Column3) = 'String'

EDIT:

For more complex rules on selecting which table has precidence, you can just use CASE statements...

(This does the same as above, but can be modified for different precidence rules.)

SELECT
  CASE WHEN t2.id IS NULL THEN -t1.id      ELSE t2.id      END  AS id,
  CASE WHEN t2.id IS NULL THEN  t1.Column1 ELSE t2.Column1 END  AS Column1,
  CASE WHEN t2.id IS NULL THEN  t1.Column2 ELSE t2.Column2 END  AS Column2
FROM
  Table1          AS t1
FULL OUTER JOIN
  Table2          AS t2
    ON  t1.Column1 = t2.Column2
    AND t1.Column2 = t2.Column2
WHERE
  COALESCE(t2.Column3, t1.Column3) = 'String'

If you are sure that there will be no duplicates, then you can boost it by replaceng UNION with UNION ALL Without the ALL the results of the queries will be sorted and the duplicates will be removed when UNIONing. see http://dev.mysql.com/doc/refman/5.0/en/union.html

This should look something like this:

SELECT 
    IFNULL(t2.Col1, t1.Col1) Col1,
    IFNULL(t2.Col2, t1.Col2) Col2,
    IFNULL(t2.Col3, t1.Col3) Col3,
    IFNULL(t2.Col4, t1.Col4) Col4,
    'Add this string to the Last Column' 
FROM 
    Table1 t1
    LEFT JOIN Table2 t2 ON t1.Column1 = t2.Column1 AND t1.Column2 = t2.Column2
WHERE 
    t1.Column3 ='String'
ORDER BY 
    Col4

If table2 columns can have normal NULL values in your database then this SELECT should start like this:

SELECT 
    IF(t2.id is NULL, t1.Col1, t2.Col1) Col1,
    IF(t2.id is NULL, t1.Col2, t2.Col2) Col1,
    IF(t2.id is NULL, t1.Col3, t2.Col3) Col1,
    IF(t2.id is NULL, t1.Col4, t2.Col4) Col1,
    'Add this string to the Last Column' 
    .
    .