question about VIEWS in 5.1.x

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: question about VIEWS in 5.1.x

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 | +256 76 91 8383 | Y! & MSN: jangita@yahoo.com
Skype: jangita | GTalk: jangita.nyagudi@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: question about VIEWS in 5.1.x

On 9/3/2010 6:23 AM, Jangita wrote:
> 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;
>>
>> ...
>>
>> (I've also tried "UNION ALL" with the same results).
>>...
>>
> 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)
> ...
>

Jangita is correct. Read the bottom of
http://dev.mysql.com/doc/refman/5.1/en/view-algorithms.html


--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: question about VIEWS in 5.1.x

On 02/09/2010 8:30 p, Hank wrote:
>>
>> Simple question about views:
>>
>>
> Hank,
> 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);
>

Wow - thanks. This works perfectly. I'm assuming I can use "call
combo(value)" in PHP and it returns the result set as if it were a
proper table?

-Hank

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: question about VIEWS in 5.1.x

On 03/09/2010 9:26 p, Hank wrote:
> On Fri, Sep 3, 2010 at 6:23 AM, Jangita wrote:
>> On 02/09/2010 8:30 p, Hank wrote:
>>>
>>> Simple question about views:
>>>
>>>
>> Hank,
>> 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);
>>
>
> Wow - thanks. This works perfectly. I'm assuming I can use "call
> combo(value)" in PHP and it returns the result set as if it were a
> proper table?
>
> -Hank
>
Yes! it should work just like a table.

--
Jangita | +256 76 91 8383 | Y! & MSN: jangita@yahoo.com
Skype: jangita | GTalk: jangita.nyagudi@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: question about VIEWS in 5.1.x

On 03/09/2010 9:27 p, Hank wrote:
> On 02/09/2010 8:30 p, Hank wrote:
>>>
>>> Simple question about views:
>>>
>>>
>> Hank,
>> 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);
>>
>
> Wow - thanks. This works perfectly. I'm assuming I can use "call
> combo(value)" in PHP and it returns the result set as if it were a
> proper table?
>
> -Hank
>
Yes it should work just like a query/table.

--
Jangita | +256 76 91 8383 | Y! & MSN: jangita@yahoo.com
Skype: jangita | GTalk: jangita.nyagudi@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org