Hi, I would use mediumint rather than int for the ID column (int has support for up to 2.1 Billion records wheras mediumint is up to 8.3 million - more efficient for your data type). I don't think the varchar will cause much of a problem. Useful section here:
http://www.mysql.com/doc/en/Data_size.html Also, do you really need 11 chars on the filesize column (that's 100GB)? If you're planning to have up to 5 million records then you only really need a length of 7 (8 to be safe). If you want a really fast search on filename then index that field. Another idea may be to turn on query caching. Here's what I would do.. CREATE TABLE FTPFILE ( ID mediumint(8) NOT NULL auto_increment, FTPSITEID mediumint(9) DEFAULT '0' NOT NULL, FILENAME varchar(254) DEFAULT 'Not defined' NOT NULL, FILEPATH varchar(254) DEFAULT 'Not defined' NOT NULL, FILEEXTN varchar(3) DEFAULT '---' NOT NULL, FILESIZE mediumint(9) NOT NULL, FILEDATE datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, PRIMARY KEY (ID), KEY IDX_FN(FILENAME) ); Also there is an interesting section on the speed of inserts here: http://www.mysql.com/doc/en/Insert_speed.html Cheers, Andrew -----Original Message----- From: Ulterior [mailto:[EMAIL PROTECTED] Sent: Saturday 31 May 2003 15:35 To: [EMAIL PROTECTED] Subject: Re: Insert query sow what would you suggest, Jerry? ( I need a very FAST search on this table's filename field) Ulterior > Don't use varchar unless you absolutely have to, that should help. > > Jerry > > ----- Original Message ----- > From: "Ulterior" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Saturday, May 31, 2003 3:13 PM > Subject: Insert query > > > > Hi, > > > > I have a database table: > > > > CREATE TABLE FTPFILE ( > > ID int(11) NOT NULL auto_increment, > > FTPSITEID int(11) DEFAULT '0' NOT NULL, > > > > FILENAME varchar(254) DEFAULT 'Not defined' NOT NULL, > > FILEPATH varchar(254) DEFAULT 'Not defined' NOT NULL, > > FILEEXTN varchar(3) DEFAULT '---' NOT NULL, > > FILESIZE int(11) NOT NULL, > > FILEDATE datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, > > > > PRIMARY KEY (ID) > > ); > > > > when this table reaches 10000 records, insert queries are very slow, > > aproximately 2 records a second. And worst of all mysql-ntd.exe > > reaches 99% of my processor timing. > > > > I am using native mysql C API mysql_query() func for inserting data. > > > > Is there any way to speedup insert querys to this table? ( I am > > planning > to > > have at least 2-5 million entries in this table ) > > > > Ulterior > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
