"mel list_php" <[EMAIL PROTECTED]> wrote on 03/18/2005 10:35:30 AM:
> 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? <snip> OK, I reviewed what you have posted so far and I found a performance killer. On the table "matches", the columns protID and drugID are declared as text. This is "bad" for searching as you can only index the first portion of any text column. Those columns should be declared as CHAR or VARCHAR or better yet, some integer value. If all 3 columns in the "matches" table are integers (INT or BIGINT, preferably UNSIGNED) then this becomes a fixed-width table and lookups become exceedingly fast. All indexes on those columns also become number-based and numeric comparisons occur *much* faster than string comparisons. I very rarely use non-numeric primary keys for just this reason. You mentioned there were indexes on the table and provided the output of DESC for the table but DESC does a very poor job of actually describing indexes. I prefer the output of SHOW CREATE TABLE xxxx\G as it gives me a complete table creation statement(Use /G and not ; to eliminate a lot of excess formatting in the output). Can you generate that for me, please? I practically guarantee that if we re-tool that table (including the indexes), our query times will drop like rocks. You can kill a single query through the commands SHOW [FULL] PROCESSLIST (to identify the # of the process you want to kill) and KILL # (using the # you just looked up). This usually drops the connection to the client running the query you killed, too (so be prepared to reconnect). http://dev.mysql.com/doc/mysql/en/show-processlist.html http://dev.mysql.com/doc/mysql/en/kill.html I just realized that we are only querying for the search conditions DRUG like 'a%' AND PROTIEN like 'a%'. If we wanted to do the OR of that (DRUG like ... OR Protien like ...) we could use a UNION query to generate tmpMatch CREATE TEMPORARY TABLE tmpMatch (key sentID) (SELECT m.sentID, m.drugID, m.protID, tsd.drugSyn, tsp.protSyn FROM matches m INNER JOIN tmpSynDrugs tsd ON tsd.nameID = m.drugID INNER JOIN tmpSynProt tsp ON tsp.nameID = m.protID) UNION (SELECT m.sentID, m.drugID, m.protID, tsd.drugSyn, tsp.protSyn FROM matches m INNER JOIN tmpSynDrugs tsd ON tsd.nameID = m.drugID INNER JOIN tmpSynProt tsp ON tsp.nameID = m.protID); Shawn Green Database Administrator Unimin Corporation - Spruce Pine
