One MySQL performance issue

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

One MySQL performance issue

L Shen

I was trying most of the day to turning up my GBrowse instance which was extremely slow and always time-out.

I found that for some reason, the two  fields are not indexed by Gbrowse when creating the database tables:

table: feature
Fields: start,end

This caused significant slowness in my installation, where I have 54 millions rows in this table.

Indexing these 2 fields improved the speed for same query from 19.4 sec to 0.1 sec.

# original table schema:

CREATE TABLE `feature`
(   `id` int(10) NOT NULL AUTO_INCREMENT,   `typeid` int(10) NOT NULL,  
`seqid` int(10) DEFAULT NULL,  
`start` int(11) DEFAULT NULL, 
 `end` int(11) DEFAULT NULL, 
 `strand` tinyint(4) DEFAULT '0',  
`tier` tinyint(4) DEFAULT NULL, 
 `bin` int(11) DEFAULT NULL,  
`indexed` tinyint(4) DEFAULT '1',  
`object` mediumblob NOT NULL,  
PRIMARY KEY (`id`),  
KEY `seqid` (`seqid`,`tier`,`bin`,`typeid`),  
KEY `typeid` (`typeid`)

If you faced same performance issue, you may want to check the index.

Lishuang Shen

Go from Idea to Many App Stores Faster with Intel(R) XDK
Give your users amazing mobile app experiences with Intel(R) XDK.
Use one codebase in this all-in-one HTML5 development environment.
Design, debug & build mobile apps & 2D/3D high-impact games for multiple OSs.
Gmod-gbrowse mailing list
[hidden email]