> -----Original Message----- > From: Ulterior [mailto:[EMAIL PROTECTED] > Sent: Saturday, May 31, 2003 7:35 AM > 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 )
Your VARCHAR columns can become CHAR columns, which should help a lot. But CHAR columns can't be longer than 255, so you're about at the limit. Are you locking the table before inserting, then using the multiple insert syntax? Those should help if you're not doing them, but I don't know what it is in the C API. How many indexes do you have? If you reduce the number of indexes, insertions will go faster, but that might slow down some of your queries, of course. If you're inserting quite a few at a time, you might disable indexing, do the inserts, then allow the indexing to happen all at once. For a large number of inserts, you might also try writing them to a file, then loading it. That's much faster for really large numbers of inserts, and you can also suspend indexing as I mentioned. I use the latter for building externally generated text indexes (frequency tables and such), generating a couple of million records at a shot. It made an enormous difference. But I'm working in Python and can't help you with the C API, as I said. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
