Re: Queries suddenly too slow
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!
Subject
Views
Written By
Posted
5119
June 06, 2005 07:02PM
2260
June 06, 2005 07:49PM
2089
June 06, 2005 07:56PM
Re: Queries suddenly too slow
2171
June 07, 2005 04:38AM
1851
June 06, 2005 07:58PM
1814
June 06, 2005 08:00PM
1767
June 07, 2005 10:25AM
1946
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.