On Mon, 26 Apr 1999, Jules Bean wrote:
> "A. Brandic" wrote:
> >
> > Hi!
> >
> > I've got a problem with MySql and I'm not sure if it is a bug or my
> > fault. I'm running MySql 3.22.21
> > on Alpha 800 with Unix 4.0 on it and MySql was installed as Binary version.
> >
> > I've created three test tables each containing just over 15000 records in
> > 14 columns.
> > If I do a simple query such as :
> > mysql> select Universe.Surname from Universe, TestTable where
> > -> ID=AdmissionNo;
> >
> > ID is unique for TestTable and it is integer(9) Primary Key and
> > auto_increment and
> > AdmissionNo is unique for Universe and it is integer(9) Primary Key and
> > auto_increment as well.
> >
> > This executes in 1.05 secs with no probs.
> >
> > But when I do a similar query:
> > mysql> select Universe.Surname from Universe, TestTable, Streams where
> > -> ID=AdmissionNo;
> >
> > Still ID and AdmissionNo are Unique for their tables and do not appear in
> > any other table but this time
> > I've inserted Streams table which is not used at all.
> > Well this time after 2min the following is displayed
> >
> > mysql: Out of memory (Needed 8160 bytes)
> > ERROR 2008: MySQL client run out of memory
> >
> > and also it disconnects me from the database that I was usinq and I have
> > to logi in again.
>
> Well, I find the error message confusing. But I can explain the
> problem...
>
> Your query wasn't useful. Adding a table to a select, and not
> specifying any conditions, will do a cartesian product. So if your
> original query returns N rows, and your Streams table has M rows, then
> this is going to return MN rows.
>
> Which could be a very big number..
>
> Jules
Sorry Jules!
You were right, it is displaying 'MN' number of rows. I did not think it
would have but it does. I've just created a small table with 5 records
in it and used it in the above query instead of Streams table and it
displaye over 60000 recods instead of 15000. My view was that the extra
table (rows from the extra table) would have been ignored after the
execution of the query.
Thanks.
Ajdin
