database design - MySQL Union in View -

i'm having 2 large tables, product , product_variants. i'm using mysql view, cause need sql statement many times. database structure cannot changed atm.

product:
id | ismaster | ean | name | price ...

product_variants:
id | pid (fk on product.id) | ismaster | ean | name | price ...

the view my_viewcontains select this:

(select * `product`) union (select * `product_variants`) 

when i'm doing query this:

select * my_view ean = '11110' 

it takes 0,5 1 second. if use content of view directly adding where each subquery, super fast (~0,004 sec):

(select * `product` ean = '11110') union (select * `product_variants` ean = '11110') 

how can use faster method in view? needed change mysql function?

tia matt

you should use mysql command analyse query use explain select statement , check table , index structure.

use command

set profiling=1;

then use select query use command

show profiles query1;

check result thing talking how time.

and other imp query are

* show create table tbl\g -- engine, indexes

• show table status 'tbl'\g -- sizes

• explain select ...\g -- clues of inefficiencies

• show variables '%buffer%'; -- ca**