List:General Discussion« Previous MessageNext Message »
From:SGreen Date:March 21 2005 5:39pm
Subject:Re: help on query/group by
View as plain text  
My reply below:

"mel list_php" <list_php@stripped> wrote on 03/21/2005 11:49:26 AM:

> Hi,
> Sorry for the late reply, out for the week-end!
> 
> Here is the information you asked for (I modified the columns' type as 
you 
> suggested)
> mysql> show create table matches\G
> *************************** 1. row ***************************
>        Table: matches
> Create Table: CREATE TABLE `matches` (
>   `protID` varchar(50) default NULL,
>   `drugID` int(11) default NULL,
>   `sentID` int(11) default NULL,
>   KEY `sentenceId` (`sentID`),
>   KEY `drugID` (`drugID`),
>   KEY `protID` (`protID`)
> ) TYPE=MyISAM
> 1 row in set (0.00 sec)
> 
> mysql> show create table sentence\G
> *************************** 1. row ***************************
>        Table: sentence
> Create Table: CREATE TABLE `sentence` (
>   `id` int(10) unsigned NOT NULL auto_increment,
>   `text` text,
>   `pmid` int(11) default NULL,
>   PRIMARY KEY  (`id`)
> ) TYPE=MyISAM
> 1 row in set (0.00 sec)
> 
> mysql> show create table synonyms\G
> *************************** 1. row ***************************
>        Table: synonyms
> Create Table: CREATE TABLE `synonyms` (
>   `nameID` varchar(50) default NULL,
>   `syn` text,
>   UNIQUE KEY `c` (`nameID`(20),`syn`(20))
> ) TYPE=MyISAM
> 1 row in set (0.01 sec)
> 
> I think I used int as much as possible,here some data samples:
> 
> INSERT INTO `matches` VALUES ('Q9UP51', 202531, 4);
> INSERT INTO `matches` VALUES ('SVC2_HUMAN', 202037, 5);
> INSERT INTO `matches` VALUES ('PF2R_SHEEP', 202096, 6);
> INSERT INTO `matches` VALUES ('CAQS_RAT', 202037, 7);
> 
> 
> INSERT INTO `sentence` VALUES (4, 'Of <NP>10 contraceptives </NP>tested 
, 
> <NP><span class=\'drug\'>Ortho-Gynol</span> </NP>was found to
> be the 
most 
> rapidly acting , followed by <NP>Cooper Creme </NP>, <a 
> href=\'http://srs.ebi.ac.uk/srsbin/cgi-bin/wgetz?[UNIPROT-acc:
> (Q9UP51)]+-id+008+-view+UniprotDateView\'>Lactikol- 
> B</a>\n, <NP>Vagi-Serol </NP>, <NP>Marvosan </NP>,
> <NP>Clinicol </NP>, 
> <NP>Jelly-X </NP>, <NP>Bor-Oxyquin </NP>, <NP>Cellikol
> </NP>, and 
> <NP>Lanteen Blue Jelly </NP>in <NP>that order </NP>. ',
> 12305459);
> 
> (the text is usually longer than 255 characters, so I think text is the 
only 
> choice, except longtext which can only be worst for indexation in my 
> opinion)
> 
> INSERT INTO `synonyms` VALUES ('202037', 'testosterone');
> INSERT INTO `synonyms` VALUES ('Q91XV1', 'gonadotropin');
> INSERT INTO `synonyms` VALUES ('202037', 'estradiol');
> INSERT INTO `synonyms` VALUES ('Q91XV1', 'gonadotropin-suppressing');
> 
> I tried to run the query you gave me (with temporary tables) it is still 
too 
> long so I guess that my only solution now is to use indexes.
> When modifying the columns' type from text to varchar, even if the text 
was 
> indexed as unique mysql  complained about duplicates, and actually there 

> were few duplicates, I thought it was as you said because only the 
beginning 
> of the text field is indexed but I had 2 cases were the names were short 

> (less than 15 characters) and the same (even the spaces).Removing one 
and 
> the index on varchar was created. Any idea why?
> 
> I will try to play with the indexes, if I understand well I'd better 
index 
> the three columns in once, because that will automatically index each of 

> them?or am I wrong?
> 
> Anyway, thank you for all your advices, I'm really learning a lot of 
things 
> with that case!
> Melanie
> 
> 
> 
> 
> 
> >From: SGreen@stripped
> >To: "mel list_php" <list_php@stripped>
> >CC: mysql@stripped
> >Subject: Re: help on query/group by
> >Date: Fri, 18 Mar 2005 12:43:06 -0500
> >
> >"mel list_php" <list_php@stripped> 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
> 

I would modify your design a bit more. I would setup a new Protein table 
and Drug table that looks like

create table xxx (
        id bigint unsigned not null auto_increment
        , shortname varchar(20)
        , fullname varchar(50)
)

(Where xxx is either Protein or Drug)That way I could change matches to 
look like:

CREATE TABLE matches (
        protien_ID bigint unsigned not null
        , drug_id bigint unsigned not null
        , sent_id bigint unsigned not null
        , KEY (drug_id, prot_id, sent_id)
        , KEY (drug_id, sent_id)
        , KEY (prot_id, sent_id)
        , KEY (sent_id, drug_id, prot_id)
)

I chose "bigint unsigned" so that you get the most possible records (
18446744073709551615) in each table. Odds are that you will run out of 
disk space long before you reach that value. I created multiple indexes on 
the matches table so that you are indexed for retrieval speed, not for 
INSERT processing.  The different combinations if KEYS represent many of 
the 3 types of queries that you will probably see most often: query on 
both drug and protein, query on either drug or protein, and query by 
sentence. I did not create a PRIMARY key as it could be possible that the 
same drug or protein is mentioned more than once in the same sentence so 
you may need to be able to store all individual references. If that is not 
the case, I would make the first KEY a PRIMARY KEY.

I also noticed that you have a type mismatch between your sentence and 
your matches table. In sentence you declare id as INT UNSIGNED but in 
matches that column is only an INT. Even if you do nothing else, you need 
to give that column the same data type in both tables.

I would also suggest you modify your synonyms table to generate a numeric 
PRIMARY KEY. 
 Create Table: CREATE TABLE `synonyms` (
  `id` bigint unsigned not null auto_increment
  `nameID` varchar(50) default NULL,
  `syn` text,
  PRIMARY KEY id
  UNIQUE KEY `c` (`nameID`(20),`syn`(20))
) TYPE=MyISAM

That way you can use a synonym.id (a number) wherever you need to 
reference a synonym and stop using the nameID. As I think more about it, 
using one table to represent BOTH drug synonyms and protein synonyms 
doesn't make good sense to me. I would probably split the synonyms into 
two separate tables, one for each data type (drug or protein). Sure some 
syn values will be the same between the two tables but it just makes 
better sense to me to do it as two tables, not one.

I know I just mentioned a lot of changes. Do not think I am asking you do 
make them all. I am just trying to let you compare how someone else would 
approach this design to your own. If this gives you some ideas, that's 
great. I just don't want you to think that I am saying "your way is wrong, 
my way is right" as there are frequently many workable solutions to any 
problem and I KNOW I am not always right.

We'll get it going... :-)

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Thread
help on query/group bymel list_php16 Mar
  • Re: help on query/group bySGreen16 Mar
    • Re: help on query/group bymel list_php18 Mar
      • Re: help on query/group bySGreen18 Mar
        • Re: help on query/group bymel list_php18 Mar
          • Re: help on query/group bySGreen18 Mar
            • Re: help on query/group bymel list_php21 Mar
              • Re: help on query/group bySGreen21 Mar