MySQL Forums
Forum List  »  Performance

Re: Queries suddenly too slow
Posted by: Benoit St-Jean
Date: June 07, 2005 04:38AM

sainandoor wrote:
> Describe tblProtein:
> +----------------+-------------+------+-----+-----
> ----+-------+
> | Field | Type | Null | Key |
> Default | Extra |
> +----------------+-------------+------+-----+-----
> ----+-------+
> | ProteinID | int(11) | NO | | 0
> | |
> | EntryName | varchar(15) | YES | | NULL
> | |
> | Accession | varchar(10) | YES | | NULL
> | |
> | ProteinName | varchar(30) | YES | | NULL
> | |
> | Organism | varchar(50) | YES | | NULL
> | |
> | SequenceLength | int(11) | YES | | NULL
> | |
> | SequenceMass | float | YES | | NULL
> | |
> | ModDate | varchar(15) | YES | | NULL
> | |
> | Sequence | text | YES | | NULL
> | |
> +----------------+-------------+------+-----+-----
> ----+-------+
>
> Describe tblProteinCoil:
> +--------------+---------+------+-----+---------+-
> ------+
> | Field | Type | Null | Key | Default |
> Extra |
> +--------------+---------+------+-----+---------+-
> ------+
> | ProteinID | int(11) | YES | | NULL |
> |
> | CoilID | int(11) | YES | | NULL |
> |
> | CoilLocation | text | YES | | NULL |
> |
> +--------------+---------+------+-----+---------+-
> ------+
>
> Describe tblCoiledCoil:
> +--------------+---------+------+-----+---------+-
> ------+
> | Field | Type | Null | Key | Default |
> Extra |
> +--------------+---------+------+-----+---------+-
> ------+
> | CoilID | int(11) | NO | | 0 |
> |
> | CoilSequence | text | YES | | NULL |
> |
> | Cluster | text | YES | | NULL |
> |
> | CoilLength | int(11) | YES | | NULL |
> |
> | Offset | char(1) | YES | | NULL |
> |
> | cluster3 | int(11) | YES | | NULL |
> |
> | cluster4 | int(11) | YES | | NULL |
> |
> | cluster5 | int(11) | YES | | NULL |
> |
> | cluster6 | int(11) | YES | | NULL |
> |
> | cluster6p | int(11) | YES | | NULL |
> |
> | decluster3 | int(11) | YES | | NULL |
> |
> | decluster4 | int(11) | YES | | NULL |
> |
> | decluster5 | int(11) | YES | | NULL |
> |
> | decluster6 | int(11) | YES | | NULL |
> |
> | decluster6p | int(11) | YES | | NULL |
> |
> +--------------+---------+------+-----+---------+-
> ------+
>
>
> Here is a sample query that is taking forever to
> run even when run directly on the mysql
> interface:
> mysql> select p.ProteinID from tblProteinCoil p
> inner join tblCoiledCoil c on p.CoilID = c.CoilID
> and decluster6p > 0 inner join tblProtein pp on
> pp.ProteinID = p.ProteinID;
>
> This same query returned the results in less than
> 2 seconds (even when querying through a PHP
> program on a IE browser). All these problems
> started right after I abruptly terminated a
> program that was trying to update the tables.
> Since then I have dropped and recreated the
> tables and reloaded the data from back up tables.


create indexes on p.CoilID, c.CoilID, pp.ProteinID and p.ProteinID

Send us the EXPLAIN of the query once you created the indexes... We might speed it up more!

Options: ReplyQuote


Subject
Views
Written By
Posted
5119
June 06, 2005 07:02PM
2260
June 06, 2005 07:49PM
2088
June 06, 2005 07:56PM
Re: Queries suddenly too slow
2170
June 07, 2005 04:38AM
1851
June 06, 2005 07:58PM
1814
June 06, 2005 08:00PM
1767
June 07, 2005 10:25AM
1945
June 07, 2005 07:10PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.