in reply to speeding up mySQL query in .pm

Does mySQL have UNION? If so you can do this: 
$sql = qq/  SELECT DISTINCT min(escore) as escore, org_id
            FROM blast, protein
            WHERE EXISTS (  SELECT id2, escore, org_id2
                            FROM blast, protein
                            WHERE id1 = id
                            AND text_id = ?
                            UNION
                            SELECT id1, escore, org_id1
                            FROM blast, protein
                            WHERE id2 = id
                            AND text_id = ? )
             GROUP BY org_id
          /;

[download]
UNION in T-SQL gets rid of duplicate rows for you. So if UNION works the same way in mySQL then you reduce the overhead of making multiple queries and you won't need a temp table.

Update: Updated the query to include the DISTINCT and GROUP BY.

Replies are listed 'Best First'.
Re: Re: speeding up mySQL query in .pm
by bean (Monk) on Aug 14, 2003 at 21:08 UTC
    Unless mySQL and T-SQL (what's T-SQL?) are very different from Oracle, I think you mean this: 
    SELECT DISTINCT min(tmp.escore) as escore, tmp.org_id
FROM (
    SELECT id2, escore, org_id2
    FROM blast, protein
    WHERE id1 = id
    AND text_id = ?
    UNION
    SELECT id1, escore, org_id1
    FROM blast, protein
    WHERE id2 = id
    AND text_id = ? ) tmp
GROUP BY tmp.org_id

    [download]
    If I knew what columns belonged to which tables I might be able to offer more advice - without that information, I get a headache every time I try to visualize what's going on here...
    Update
    With the WHERE EXISTS, you end up selecting from the original blast and protein tables if the subselect returns any rows, when what you want to do is select from the subselect results.
[reply]
[d/l]
      Good call bean. Your query is more succinct. I cast you a vote :). T-SQL = Transact-SQL = MS flavor of SQL in MS SQL Server.

      Anonymous Monk: is sounds like you need to put a use DBI; at the top of your program. Since you're a self proclaimed novice you make want to check this out Before asking a database related question .... It's a great read.

      Good luck.

[reply]
Re: Re: speeding up mySQL query in .pm
by Anonymous Monk on Aug 14, 2003 at 21:36 UTC
    Monktim, I'm trying to work your code into mine and I'm having some
    problems any help would be appreciated. Here's what I tried:
    my $orf_id = $pt->get("$i,2");
    #$dbh->do(q{ CREATE TEMPORARY TABLE IF NOT
    EXISTS
    moo(id INT UNSIGNED, escore DOUBLE, org_id
    MEDIUMINT)});


    $sql1 = qq/ SELECT DISTINCT min(escore) as escore,
    org_id
    FROM blast, protein
    WHERE EXISTS ( SELECT id2, escore, org_id2
    FROM blast, protein
    WHERE id1 = id
    AND text_id = ?
    UNION
    SELECT id1, escore, org_id1
    FROM blast, protein
    WHERE id2 = id
    AND text_id = ? )
    GROUP BY org_id
    /;
    $sth2->prepare($sql1);
    $sth2->execute($orf_id);
    with the above I got the following error:

    Background Error: Can't locate object method "prepare" via package "DBI::st" (p rhaps you forgot to load "DBI::st"?) at C:/Perl/site/lib/RG/PropTable.pm line 1 75.
    I've inhereted the code and a novice at best.
    Any ideas on where the problem is?
    Thanks,
    Mark
[reply]
Re: Re: speeding up mySQL query in .pm
by Apocalypse (Initiate) on Aug 16, 2003 at 07:08 UTC
    Yes, MySQL does have UNION, but in versions > 4.x, watch out for that...
[reply]

In Section Seekers of Perl Wisdom