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)