List:General Discussion« Previous MessageNext Message »
From:Hank Date:September 2 2010 6:30pm
Subject:question about VIEWS in 5.1.x
View as plain text  
Simple question about views:

I have a view such as:

         create view combo as
         select * from table1
         union
         select * from table2;

Where table1 and table2 are very large and identical and have a
non-unique key on field "id"..

when I do a:

select * from combo where id='value'  ;

the system seems to be doing a table scan of one or both tables.. I
can't even do an:

explain select * from combo where field='value' ;

the system seems to hang on the explain.  SHOW PROCESSLIST says the
explain is "Sending data ".

Issuing either one of the view components with the where clause
returns results in a fraction of a second (pretty much a full indexed
lookup)

I know when I used to use Oracle, the where clause would be applied to
all parts of the view, but in this case, I can't even figure out what
MySQL is trying to do.

(I've also tried "UNION ALL" with the same results).

Any suggestions on how to query both tables using the indexed and the
view at the same time?  That was my intention.

-Hank
Thread
question about VIEWS in 5.1.xHank2 Sep
  • Re: question about VIEWS in 5.1.xJangita3 Sep
    • Re: question about VIEWS in 5.1.xMySQL)3 Sep
Re: question about VIEWS in 5.1.xHank3 Sep
  • Re: question about VIEWS in 5.1.xJangita6 Sep
Re: question about VIEWS in 5.1.xJangita6 Sep