Karl Pielorz wrote:
> Hi All,
> I have a MySQL database, and a seperate C program that imports records into
> that database... At the start of the import the system imports around 1000
> records per seconds, but as time goes on - the insert speed slows down
> (typically down to around 200/sec - after importing around 300,000 records)...
> I've removed all the Unique indexes in the system, and all but 1 table follow
> a simple:
> CREATE TABLE test (
> test_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
> test_name CHAR(255) NOT NULL,
> PRIMARY KEY (test_id),
> INDEX idx_test_name (test_name)
> (ditto for 'sibling', 'path', 'other', 'parent' etc.)
> There is one other 'main' table which is simply,
> CREATE TABLE main_table (
> record_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
> sibling_id MEDIUMINT UNSIGNED NOT NULL,
> parent_id MEDIUMINT UNSIGNED NOT NULL,
> path_id MEDIUMINT UNSIGNED NOT NULL,
> other_id MEDIUMINT UNSIGNED NOT NULL,
> PRIMARY KEY (record_id),
> INDEX idx_sibling_id (sibling_id),
> INDEX idx_parent_id (parent_id),
> INDEX idx_path_id (path_id),
> INDEX idx_other_id (other_id)
> Each of the 'test' type tables have around 40,000 records in (fully
> populated), and the main table has around 1.2 million records in (fully
> It seems as the tables get more populate the speed slows way down. We are
> currently running MySQL 3.22.22 (compiled from the FreeBSD port, using native
> threads under FreeBSD 4.0-Current).
> Is there anything I can do to stop this apparent slowdown?
> Also, are 'mediumints' only going to save space, rather than time in the
> database? (i.e. 24 bit compares/work being more CPU costly than 32 bit/native
> The machine running this is a P2-450, with 512Mb of RAM. MySQL and the import
> program aren't memory starved, and the machine doesn't swap... :)
> Karl Pielorz
To speed up inserts you should drop all INDEX's and KEY's before starting the import.
After you are done, add each Index again.
MEDIUMINT is faster loaded from/saved to disk.
I don't think you will gain something on processing by the CPU.