List:General Discussion« Previous MessageNext Message »
From:Tonu Samuel Date:April 9 2000 10:35am
Subject:Re: MySQL/PostgreSQL Speed issues
View as plain text  
Dave wrote:


> My INSERT is similar to the following:
> 
> INSERT into table (field1, field2, field3....) values (data1, data2,
> data3...)
> 
> nothing fancy, nothing wierd...

Do the fancy way ;)

INSERT INTO  table (field1, field2, field3....) values (data1,
data2,data3...),(data1, data2, data3),(data1, data2, data3),(...

This speeds it up for few times. I am collecting something like 5000
records into one clause and then inserting them with one command. I am
getting speed of 12500 records inserted in second on Pentium II-350
computer. Also you can get more speed by removing indexes before
inserting and/or putting LOCK statements around many inserts and/or
creating table with flag DELAY_KEY_WRITES=1 and increasing some buffers
as described in manual.

You have two bottlenecks: text parsing and disk IO. You are sitting now
on text parsing bottleneck. Parser is one of the slowest things in SQL-s
and can't be done much better. Everything else in MySQL is mostly
perfect or at least pretty good.

-- 
+----------------------------------------------------------------+
| TcX  ____  __     _____   _____  ___                           |
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|     Tõnu Samuel           |
|    /*/ /*/ /*/   \*\_   |*|   |*||*|     tonu@stripped        |
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|     Tallinn, Estonia      |
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|____                       |
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                      |
|             /*/             \*\                Developers Team |
+----------------------------------------------------------------+
Thread
MySQL/PostgreSQL Speed issuesDave9 Apr
  • Re: MySQL/PostgreSQL Speed issuesAaron Holtz9 Apr
  • Re: MySQL/PostgreSQL Speed issuesTonu Samuel9 Apr
    • Re: MySQL/PostgreSQL Speed issuesTim Bunce10 Apr
  • Re: MySQL/PostgreSQL Speed issuesWesley Darlington9 Apr
  • Re: MySQL/PostgreSQL Speed issuesDave10 Apr