[Gmod-gbrowse] 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