mysql - How can optimize this VIEW Statement? -

mysql - How can optimize this VIEW Statement? -

i have view slow. don't view statements there lot of joins , union.

here view statement.

create view newview select t2.* table1 t1 bring together table2 t2 on t1.column1 = t2.column1 , t1.column2 = t2.column2 t1.column3 !='string' union select t1.*, 'add string lastly column' table1 t1 left bring together table2 t2 on t1.column1 = t2.column1 , t1.column2 = t2.column2 t2.column1 null or t1.column3 ='string' order column 4

basically thought if record exists in table1 , table2, record table2 should overlay record table1. how can optimize this?

i have primary key id int not null auto_increment primary key in both tables not sure how can integrate view. want view have primary key or composite key. cannot utilize other columns columns can have null , duplicate values.

you can bring together , compare, using outer join, utilize coalesce prefer t2 t1.

to retain unique key, , assuming id's positive, can create 1 table's id's negative.

select coalesce(t2.id, -t1.id) id, coalesce(t2.column1, t1.column1) column1, coalesce(t2.column2, t1.column2) column2 table1 t1 total outer bring together table2 t2 on t1.column1 = t2.column2 , t1.column2 = t2.column2 coalesce(t2.column3, t1.column3) = 'string'

edit:

for more complex rules on selecting table has precidence, can utilize case statements...

(this same above, can modified different precidence rules.)

select case when t2.id null -t1.id else t2.id end id, case when t2.id null t1.column1 else t2.column1 end column1, case when t2.id null t1.column2 else t2.column2 end column2 table1 t1 total outer bring together table2 t2 on t1.column1 = t2.column2 , t1.column2 = t2.column2 coalesce(t2.column3, t1.column3) = 'string'

mysql sql view query-optimization