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
> 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
> 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.
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)
(SELECT * FROM table1 WHERE id = theid)
(SELECT * FROM table2 WHERE id = theid);
then calling it using
Jangita | +256 76 91 8383 | Y! & MSN: jangita@stripped
Skype: jangita | GTalk: jangita.nyagudi@stripped