Re: help on query/group by

[mel list_php]

Hi again,
Thanks for the explanation about the join and the group by.
I wanted to test your query (almost a simple copy/paste :-)) ).
The first 2 queries are ok, but the third one still is too long :
mysql> CREATE  TEMPORARY  TABLE tmpSynDrug(  KEY ( nameID (20))  ) SELECT 
nameID, max( syn )  AS drugSyn
   -> FROM synonyms
   -> WHERE syn
   -> LIKE  'a%'
   -> GROUP  BY nameID;
Query OK, 9693 rows affected (1.07 sec)
Records: 9693  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TEMPORARY TABLE tmpSynProt (key (nameID(20)))
   -> SELECT nameID, max(syn) as protSyn
   -> FROM synonyms
   -> WHERE syn LIKE 'a%'
   -> GROUP BY nameID;
Query OK, 9693 rows affected (1.03 sec)
Records: 9693  Duplicates: 0  Warnings: 0
mysql>
mysql> CREATE  TEMPORARY  TABLE tmpMatch(  KEY ( sentID)  ) SELECT m.sentID, 
m.drugID, m.protID, tsd.drugSyn, tsp.protSyn
   -> FROM matches m
   -> INNER  JOIN tmpSynDrug tsd ON tsd.nameID = m.drugID
   -> INNER  JOIN tmpSynProt tsp ON tsp.nameID = m.protID;

I've tried once this morning (GMT time), it ran for one hour nothing I 
killed mysql (btw, how can I kill only one query when mysql "freezes"?I 
aborted but then mysqladmin shutdown didn't work anymore..)
I thought it was maybe because I have other heavy stuff running, but I tried 
a second time now and it's been running for 2 hours now, with almost nothing 
else on the desktop.
So apparently the join between matches and the other tables is still too 
heavy....
Any idea?


From: [EMAIL PROTECTED]
To: "mel list_php" <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: Re: help on query/group by
Date: Fri, 18 Mar 2005 09:14:02 -0500
Responses embedded below
"mel list_php" <[EMAIL PROTECTED]> wrote on 03/18/2005 05:57:29 AM:
> Hi Shawn,
>
> Thank you very much, I'm impressed by the time you took to answer me,
and
> the quality of the reply!!!
> I forwarded the answer to my friend.
> I'm wondering, I knew the mechanism of temporary tables, but as I've
never
> used it I was trying the left join way.
> Here is a summary of my questions:
> - why using inner join here?is there any difference with using a left
join?I
> thought using a left join would decrease the number of results.
The primary difference between a LEFT JOIN and an INNER JOIN is that with
an INNER JOIN matching records MUST exist in both tables before they are
considered for evaluation by the WHERE clause. You usually retrieve MORE
records with a LEFT JOIN than an INNER JOIN but that depends on your data,
too. In no case can an LEFT JOIN return fewer records than an INNER JOIN,
all other conditions being equal.
> - do you know why without group by my query was running very fast and
become
> so slow with the group by?when it does a group by it's scanning the
whole
> table or an other reason?
The GROUP BY clause requests that the engine make another processing pass
through the records that satisfy your WHERE clause conditions in order to
aggregate records according to the columns you specified. It's that second
pass and the processing that occurs within it that makes a "grouped" query
slower to finish than an "ungrouped" one. (NOTE: Some ungrouped query
results are so large that a grouped result may actually be _useful_ sooner
due to less data transfer between the server and your application)
> - I don't know if his version of mysql supports subqueries, but I was
> wondering if it is possible to replace the temporary tables by
subqueries
> and keeping the same efficiency (my friend told me he would like to have
> only one sql query).
I have found very few cases where subqueries outperformed temp (or
special-purpose, permanent) tables especially when working with larger
amounts of data. Of course, subquery performance varies according to the
nature of the subquery (can it be evaluated just once or does it have to
have to be evaluated for each and every row of the result), the complexity
of the subquery, and the hardware your server is on. The only way to know
for sure is to develop a subquery version of this query and test it with
your/their hardware.
> Once again thank you very much for your help, I will give temporary
tables
> an other chance!!!
> Melanie
>
You are most welcome!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
> <BIG snip>
_________________________________________________________________
Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]