varchar doesn't cause a problem, its a case of not using it speeds things up.
As with all things, horses for courses, if you don't need to use it, don't. Jerry ----- Original Message ----- From: "Andrew Braithwaite" <[EMAIL PROTECTED]> To: "'Ulterior'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Saturday, May 31, 2003 8:05 PM Subject: RE: Insert query > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
