hi.
by skimming that query, the first thing that will take a long time is the
'description like "%GABA%"' part...that's a pretty inefficient method of
searching due to the wildcard at the beginning of the string.
if there are no indexes on the tables, you should probably add some.
"show index from tablename" will tell you for sure.
check out the mysql manual section re: indexes for more information...also
use the keyword EXPLAIN before the select query to find out where/if indexes
are being used and where they would likely be the most helpful.
hth.
-ravi.
-----Original Message-----
From: Bryan Coon [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 27, 2001 4:20 PM
To: '[EMAIL PROTECTED]'
Subject: Help needed with Query
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
---------------------------------------------------------------------
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
