Please do a "SHOW FIELDS FROM" and "SHOW KEYS FROM" on all tables involved
so we can see what's going on here. Also, a warning, if desacription is
indexed, by doing LIKE '%GABA%' with wildcard at beginning, it won't use
index's.
Would also help if you did an EXPLAIN on that query and sent that.
----- Original Message -----
From: "Bryan Coon" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, April 27, 2001 1:20 PM
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
