I am working on a mysql database with many large tables (1.5 million rows on
some) and came across a beeg problem.
I have need for a query like this:
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;
Which seems insane. This 4 table join in another situation would become a 6
table join. As it is, it takes 1min 10.60 seconds for this query to return
one result. Clearly I am not a MySQL expert, and even though this query
works, there must be a better way to do this.
Is it a matter of indexing? A matter of poor schema design? Poor query
design? Is it unavoidable?
As far as I know, none of the tables are indexed (I did not set up the
schema).
Please let me know if more info is required to help with this.
Thanks,
Bryan
---------------------------------------------------------------------
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
