Slow INSERT INTO Query when using Indexes

[monkey64]

I have a php script which updates the Mysql Database. I've been having major problems as the process takes 10-20 mins to update 5k records on the live webserver. Locally, it takes 0.5 second. In desperation, I tried using just one Insert query, which worked well for the first 250 rows and then failed. I was able to tweak my home Mysql config, but I can't change the config of the live web server. I'm using Indexes on my tables; one Primary on my id field, and a couple of other Indexes (code below).

I have the same script (with Indexes)running here:

http://www.windcases.co.uk/update.php

and it takes 5-10 seconds! So I was assuming it was a hosting issue, but I ran the same script on the problem server without the Indexes, and it took just 10 seconds. The hosting company are not that willing to help.

So what's the answer? Does my code need a re-think or is it a hosting issue?

// add a table to the selected database
$result="CREATE TABLE accessories (

id int(5) NOT NULL,
catno varchar(255) NOT NULL,
family varchar(255) NOT NULL,
section varchar(255) NOT NULL,
header varchar(255) NOT NULL,
category varchar(255) NOT NULL,
description varchar(255) NOT NULL,
addinfo varchar(255) NOT NULL,
stocklevel int(5) NOT NULL,
vrate FLOAT(10) NOT NULL,
single varchar(10) NOT NULL,
picture varchar(40) NOT NULL,
post int(5) NOT NULL,
uqty varchar(10) NOT NULL,
unit varchar(50) NOT NULL,
price FLOAT(10) NOT NULL,
vat FLOAT(10) NOT NULL,
KEY id (id), KEY catno (catno), KEY section (section), KEY description (description), KEY category (category), KEY header (header)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";


mysql_query("INSERT INTO accessories VALUES ('0', 'AA10L', 'group', 'Accessories', 'Monkey', 'Nuts', 'Brown and hairy', '', '28', '0.15', 'Pack of 6', 'no_image.jpg', '0', '1', 'Pack of 6', '4.16', '0.62')");
mysql_query("INSERT INTO accessories VALUES ('1', 'AA10S', 'group', 'Accessories', 'Cat', 'Cat Food', 'Comes in a tin', 'Comes in a tin', '74', '0.15', 'Pack of 6', 'aa10s.jpg', '0', '1', 'Pack of 6', '4.13', '0.62')");
[/code/

Any ideas

 

[r937]

try batching the rows to be inserted, instead of inserting them one at a time

INSERT INTO accessories VALUES
( 0 , 'AA10L', 'group', 'Accessories', ... )
,( 1 , 'AA10S', 'group', 'Accessories', ... )
,( 2 , 'AA10Z', 'group', 'Accessories', ... )
, ...

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[jaxtell]

I think there are some commands that can be run to optimize table structures and free space. The status tab in phpMyadmin might provide some insight to problem areas. Maybe its worth exporting the entire database and recreating it to see if that solves the issue.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[monkey64]

Actually I solved the problem, purely by chance...
I added a few more indexes to the tables and notices that my updates were taking 20+ minutes. I removed all KEYS and managed to update in 2 minutes.

Adding the following to the end of the code, sped the process up tenfold, although I am not sure why?

mysql_query("ALTER TABLE `xaccessories` ADD PRIMARY KEY (`id`)");
mysql_query("ALTER TABLE `xaccessories` ADD INDEX (`catno`)");
mysql_query("ALTER TABLE `xaccessories` ADD INDEX (`section`)");
mysql_query("ALTER TABLE `xaccessories` ADD INDEX (`description`)");
mysql_query("ALTER TABLE `xaccessories` ADD INDEX (`category`)");
mysql_query("ALTER TABLE `xaccessories` ADD INDEX (`header`)");