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
Hello:

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`)
) ENGINE=MyISAM AUTO_INCREMENT=1371 DEFAULT CHARSET=latin1


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.
http://pubads.g.doubleclick.net/gampad/clk?id=254741551&iu=/4140
_______________________________________________
Gmod-gbrowse mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-gbrowse