MySQL Forums
Forum List  »  Performance

Re: Queries suddenly too slow
Posted by: sainandoor
Date: June 06, 2005 07:56PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
5119
June 06, 2005 07:02PM
2260
June 06, 2005 07:49PM
Re: Queries suddenly too slow
2088
June 06, 2005 07:56PM
2171
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.