Re: Help needed with Query UPDATE INFO
Ok, a few things I see...first off, slap some keys into those tables on the join portions. Secondly, upon examining your query a second time, your never joining subsnp and locuslink. I see your trying to use a full join, but you didn't put the criteria for it in the where clause. That would cause some VERY weird join results, and may be your problem. Try adding in the join clause in the where clause. - Original Message - From: "Bryan Coon" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, April 27, 2001 2:52 PM Subject: RE: Help needed with Query UPDATE INFO > Okay, here is the full monty from the database regarding the query in > question, any suggestions on how to improve any of it are greatly > appreciated! > > Also, if any more info is needed, just let me know. > > Thanks! > Bryan > > > QUERY: > mysql> select distinct subsnp_pk as sqnmid, c.chrom, chrompos, source as > panel, null as first_pcrp, null as second_pcrp, null as prb_seq, null as > term, null as validation, null as freq, null as population from locuslink > l, subsnp left outer join chromosome_position c on subsnp_pk = c.snp_fk > left outer join locus_annotation a on subsnp_pk = a.snp_fk where a.locusid > = substring(l.locusid, 4) and description like '%GABA%' order by subsnp_pk; > > EXPLAIN: > ++--+---+--+-+--+---+--- > --+ > | table | type | possible_keys | key | key_len | ref | rows | Extra > | > ++--+---+--+-+--+---+--- > --+ > | subsnp | ALL | NULL | NULL |NULL | NULL | 1201 | Using > temporary; Using filesort | > | l | ALL | NULL | NULL |NULL | NULL | 21294 | where used > | > | c | ALL | NULL | NULL |NULL | NULL | 579 | > | > | a | ALL | NULL | NULL |NULL | NULL | 704 | where > used; Distinct| > ++--+---+--+-+--+---+--- > --+ > 4 rows in set (0.00 sec) > > DESCRIBE: > mysql> describe locuslink; > ++--+--+-+-+---+ > | Field | Type | Null | Key | Default | Extra | > ++--+--+-+-+---+ > | LOCUSID| varchar(12) | | | | | > | SYMBOL | varchar(15) | YES | | NULL| | > | INTERIM_SYMBOL | varchar(15) | YES | | NULL| | > | MIM_NUMBER | varchar(15) | YES | | NULL| | > | CHROM | varchar(6) | YES | | NULL| | > | BAND | varchar(20) | YES | | NULL| | > | DESCRIPTION| varchar(150) | YES | | NULL| | > | SPECIES| varchar(10) | YES | | NULL| | > | SOURCESEQ | varchar(15) | YES | | NULL| | > ++--+--+-+-+---+ > 9 rows in set (0.01 sec) > > mysql> describe subsnp; > ++-+--+-+-+---+ > | Field | Type| Null | Key | Default | Extra | > ++-+--+-+-+---+ > | SUBSNP_PK | int(11) | | PRI | 0 | | > | SOURCE | varchar(40) | | | | | > | SOURCEID | varchar(20) | | | | | > | SNPPOSITION| int(11) | | | 0 | | > | TOTALSEQLENGTH | int(11) | | | 0 | | > | VARIATION | varchar(20) | YES | | NULL| | > | NUM_ALLELES| int(11) | YES | | NULL| | > ++-+--+-+-+---+ > 7 rows in set (0.00 sec) > > mysql> describe chromosome_position; > +--++--+-+-+---+ > | Field| Type | Null | Key | Default | Extra | > +--++--+-+-+---+ > | SNP_FK | int(11)| | | 0 | | > | CHROM| varchar(5) | YES | | NULL| | > | CHROMPOS | int(11)| YES | | NULL| | > +--++--+-+-+---+ > 3 rows in set (0.00 sec) > > mysql> describe locus_annotation; > +-+-+--+-+-+---+ > | Field | Type| Null | Key | Default | Extra | > +-+-+--+-+-+---+ > | SNP_FK | int(11) | | | 0 | | > | LOCUS
RE: Help needed with Query UPDATE INFO
Okay, here is the full monty from the database regarding the query in question, any suggestions on how to improve any of it are greatly appreciated! Also, if any more info is needed, just let me know. Thanks! Bryan QUERY: mysql> select distinct subsnp_pk as sqnmid, c.chrom, chrompos, source as panel, null as first_pcrp, null as second_pcrp, null as prb_seq, null as term, null as validation, null as freq, null as population from locuslink l, subsnp left outer join chromosome_position c on subsnp_pk = c.snp_fk left outer join locus_annotation a on subsnp_pk = a.snp_fk where a.locusid = substring(l.locusid, 4) and description like '%GABA%' order by subsnp_pk; EXPLAIN: ++--+---+--+-+--+---+--- --+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+---+--- --+ | subsnp | ALL | NULL | NULL |NULL | NULL | 1201 | Using temporary; Using filesort | | l | ALL | NULL | NULL |NULL | NULL | 21294 | where used | | c | ALL | NULL | NULL |NULL | NULL | 579 | | | a | ALL | NULL | NULL |NULL | NULL | 704 | where used; Distinct| ++--+---+--+-+--+---+--- --+ 4 rows in set (0.00 sec) DESCRIBE: mysql> describe locuslink; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | LOCUSID| varchar(12) | | | | | | SYMBOL | varchar(15) | YES | | NULL| | | INTERIM_SYMBOL | varchar(15) | YES | | NULL| | | MIM_NUMBER | varchar(15) | YES | | NULL| | | CHROM | varchar(6) | YES | | NULL| | | BAND | varchar(20) | YES | | NULL| | | DESCRIPTION| varchar(150) | YES | | NULL| | | SPECIES| varchar(10) | YES | | NULL| | | SOURCESEQ | varchar(15) | YES | | NULL| | ++--+--+-+-+---+ 9 rows in set (0.01 sec) mysql> describe subsnp; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | SUBSNP_PK | int(11) | | PRI | 0 | | | SOURCE | varchar(40) | | | | | | SOURCEID | varchar(20) | | | | | | SNPPOSITION| int(11) | | | 0 | | | TOTALSEQLENGTH | int(11) | | | 0 | | | VARIATION | varchar(20) | YES | | NULL| | | NUM_ALLELES| int(11) | YES | | NULL| | ++-+--+-+-+---+ 7 rows in set (0.00 sec) mysql> describe chromosome_position; +--++--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--++--+-+-+---+ | SNP_FK | int(11)| | | 0 | | | CHROM| varchar(5) | YES | | NULL| | | CHROMPOS | int(11)| YES | | NULL| | +--++--+-+-+---+ 3 rows in set (0.00 sec) mysql> describe locus_annotation; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | SNP_FK | int(11) | | | 0 | | | LOCUS | varchar(20) | YES | | NULL| | | LOCUSID | int(11) | YES | | NULL| | +-+-+--+-+-+---+ 3 rows in set (0.00 sec) mysql> KEYS: mysql> show keys from locuslink; Empty set (0.00 sec) mysql> show keys from subsnp; +++--+--+-+---+- +--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +++--+--+-+---+- +--++-+ | subsnp | 0 | PRIMARY |1 | SUBSNP_PK | A | 1201 | NULL | NULL | | +++--+--+-+---+- +--++-+ 1 row in set (0.00 sec) mysql> show keys from chromosome_position; Empty set (0.00 sec) mysql> show keys from locus_annotation; Empty set (0.00 sec) mysql> - Before posting, please check: http://www.mysql.com