Re: question about VIEWS in 5.1.x

[Jangita]

On 02/09/2010 8:30 p, Hank wrote:
> 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
>
Hank,
I think mysql is selecting ALL the records from both tables then 
applying the where clause to all the data from table 1 and table 2 (I 
think - guys correct me if I'm wrong)

Have you tried "running away from the problem :-)" by doing...

CREATE PROCEDURE `combo`(theid INT)
BEGIN
	(SELECT * FROM table1 WHERE id = theid)
	UNION
	(SELECT * FROM table2 WHERE id = theid);
     END$$

then calling it using

call combo(value);

?

-- 
Jangita | [phone] | Y! & MSN: jangita@stripped
Skype: jangita | GTalk: jangita.nyagudi@stripped