Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Re: speeding up mySQL query in .pm

by monktim (Friar)
on Aug 14, 2003 at 19:15 UTC ( [id://283971]=note: print w/replies, xml ) Need Help??


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 /;
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
    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.
      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.

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
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...

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://283971]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others cooling their heels in the Monastery: (4)
As of 2024-03-29 12:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found