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/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
