Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi there monks,

My problem is mainly a mysql one, but I hope that someone will still answer me?! I am trying to administer queries to my mysql database. There are 6 tables with two columns each containing pairs of numbers.

My problem is when querying myql directly from the command line I get the following:

mysql> SELECT AM.AM_A, AM.AM_M FROM AM,AR,SA,MR,SM,SR WHERE AM.AM_A !=
+ SA.SA_A && AM.AM_A != AR.AR_A && AM.AM_M != MR.MR_M && AM.AM_M != SM
+.SM_M; <P>     mysql: Out of memory (Needed 8164 bytes)<P>
ERROR 2008: MySQL client run out of memory

or sometimes:
ERROR 1030: Got error 28 from table handler

[download]

Using DBI through perl to make the query gives me the same problems! Does any one know of a less memory-intensive way of doing this? - either perl-based or mysql command line?

Kind regards to you all

2006-02-11 Retitled by g0n, as per Monastery guidelines
Original title: 'perl, memory and mysql'

Replies are listed 'Best First'.
Re: (OT) perl, memory and mysql
by rnahi (Curate) on Feb 10, 2006 at 16:30 UTC

    Of course you are running out of memory. Your query is generating five cartesian products!

    If you don't set a join clause for each table, your query will be producing a huge number of rows. And this would slurp up all your available resources, no matter which DBMS you are using.

    For example, if each table had 50 rows, then your query would generate 15,625,000,000 (fifteen billion rows).
    With 100 rows per table, you'd get 1,000,000,000,000 rows (one thousand billion rows!). You get the idea.

    Go get a SQL tutorial before continuing any further.

[reply]
Re: (OT) perl, memory and mysql
by misterb101 (Sexton) on Feb 10, 2006 at 18:48 UTC
    Hi,
    rnahi is right. If you want to see how many rows are effected and how the joins are done execute a 
    explain SELECT AM.AM_A, AM.AM_M FROM AM,AR,SA,MR,SM,SR WHERE AM.AM_A !
+=
+ SA.SA_A && AM.AM_A != AR.AR_A && AM.AM_M != MR.MR_M && AM.AM_M != SM
+.SM_M;

    [download]
    in your command line mysql client. Any query that has table joins like 'ALL' are bad news. These are the cartesian product joins rnahi talks about.
    for more information about this check for instance http://dev.mysql.com/doc/refman/4.1/en/query-speed.html
    if the explain query gives you a reasonable amount of rows and joins and you still have a problem with DBI, return to the monastry!
    --
    Cheers,
    Rob
[reply]
[d/l]
Re: (OT) perl, memory and mysql
by Anonymous Monk on Feb 11, 2006 at 00:51 UTC
    In the spirit of give a man a fish... 
    SELECT        AM.AM_A, AM.AM_M

FROM        AM
LEFT JOIN    AR ON AR.AR_A=AM.AM_A
LEFT JOIN    SA ON SA.SA_A=AM.AM_A
LEFT JOIN    MR ON MR.MR_M=AM.AM_M
LEFT JOIN    SM ON SM.SM_M=AM.AM_M
#LEFT JOIN    SR ON SR.SR_M=AM.AM_M

WHERE        AR.AR_A IS NULL
AND        SA.SA_A IS NULL
AND        MR.MR_M IS NULL
AND        SM.SM_M IS NULL
#AND        SR.SR_M IS NULL

    [download]
    Something close to this may be closer to the result you want. I didn't see you use a WHERE clause vs the SR table, so I left it commented out.
[reply]
[d/l]
      Hi,

      thanks for the response. Will using left joins and 'NULL' values give me the same response that my original query did (in the event of unlimited memory!) ?

      I don't really understand the use of NULL in this instance because I have no NULL values in my database and was originally checking for where two values weren't equal.

      Thanks again!

[reply]
      Hi again, I am still trying to get to grips with left joins. How would it work for the following query?

      select * from AM, AR, SA, SM, MR, SR where AR.AR_A=AM.AM_A and AR.AR_A
+=SA.SA_A and SR.SR_R=MR.MR_R and SR.SR_R=AR.AR_R and AM.AM_M=MR.MR_M 
+and AM.AM_M=SM.SM_M   and SM.SM_S=SA.SA_S and SM.SM_M=SR.SR_S;

      [download]
      Kind regards!
[reply]
[d/l]

Back to Seekers of Perl Wisdom