Hi,
I am a newbie to MySQL and and have managed things so far. I need some help in database tuning though.
I have a single MyISAM table with 2 indexes as reflected by the create table statement below.
CREATE TABLE `tablec` (
`SlNo` bigint(32) NOT NULL auto_increment,
`OwnersName` varchar(100) NOT NULL default '',
`Address` varchar(250) default NULL,
`City` varchar(50) default NULL,
`ZipCode` varchar(14) default NULL,
`PropertyId` varchar(50) NOT NULL default '',
`HeldBy` varchar(50) NOT NULL default '',
`ReportedBy` varchar(250) default NULL,
`Amount` varchar(20) NOT NULL default 'Not disclosed',
`Amount1` varchar(10) NOT NULL default '100',
`AccountType` varchar(100) NOT NULL default 'Cash or Other Asset',
PRIMARY KEY USING BTREE (`SlNo`),
KEY `HeldBy` USING BTREE (`HeldBy`),
KEY `OwnersName` USING BTREE (`OwnersName`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=60000000 AVG_ROW_LENGTH=200;
Currently a "select where" query on OwnersName takes a lot of time for some of more popular names like SMITH which itself might have about 100,000 results. The query is "select * from tablec where OwnersName like 'SMITH%' LIMIT 0,50, displaying top 50 records only.
The system is a Windows 2003 Server with over 1GB of RAM and has about 40GB free space. I read about tweaking of the variables key_buffer_size and read_buffer_size in order to speed up execution. Could anyone advise me on the best possible variable setting for the mentioned scenario. Mostly there will be select queries on the database, unless I am uploading more records into the table or updating them.
Any help would be appreciated.
Regards,
Vibhu Bansal.
http://www.findlostmoney.net
http://www.itsyssolutions.com