Skip navigation links

MySQL Forums :: Performance :: mysql getting slow after 200,000 records... :(


Advanced Search

mysql getting slow after 200,000 records... :(
Posted by: amir amir ()
Date: June 04, 2006 09:54AM

hello there.
im having a problem. im building a forum system and i got for now about 200,000 records and im aim to go to 10 million in till the next year.

the problem is that when im reaching to 200,000 records, its starting to be slow (simple select match against query takes 10 secounds and more)

here is the database stracture:
`id` int(30) NOT NULL auto_increment,
`writernick` varchar(100) default NULL,
`msgsubject` text NOT NULL,
`msgbody` text NOT NULL,
`msglink` varchar(255) default NULL,
`msgday` int(2) default NULL,
`msgmonth` int(2) default NULL,
`msgyear` int(4) default NULL,
`msghour` int(2) default NULL,
`msgminute` int(2) default NULL,
`fatherid` varchar(100) default NULL,
`url` mediumtext,
`forum_id` int(10) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `fath` (`fatherid`),
FULLTEXT KEY `msgbody` (`msgbody`)


explain:
msgday and all the time & date fields designed to make search easier (i know i can use timestamp of unix but its more easy to do delete records where year=03 ... or am i worng?)

the table is myisam... is that good? ill be happy to hear how to improve that stracture (i can drop the db and redesign it if its need)

Options: ReplyQuote


Subject Views Written By Posted
mysql getting slow after 200,000 records... :( 1087 amir amir 06/04/2006 09:54AM
Re: mysql getting slow after 200,000 records... :( 772 KimSeong Loh 06/05/2006 10:42AM


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.