delay when INSERTing more than once

[vmarellano]

Hi,

I am using the script below to update a table with some
information. However, after I ran the script once, unless
I wait for 1-2 minutes, the script will not enter new
information into my table when I ran it again.

Does anybody know why? Is there something
I can change in the script to avoid this delay?

Thanks a million in advance,

-Victor Arellano

===============================================================
$link = mysql_connect("localhost", "vmarellano", "******") or DIE("Unable to connect to database");
@mysql_select_db("vmarellano") or DIE("Unable to select database");

$result = mysql_query("Select * from aredry where lote='$lote' and job='$job'");
if(!mysql_numrows($result)) {

#crea un nuevo lote
$result = mysql_query("insert into aredry(cia,job,lote,fecha,plan,direccion,ciudad,ar_1_2X4X8R,
ab_1_2X4X8R,ar_1_2X4X12R,ab_1_2
X4X12R,ar_5_8X4X8FC,ab_5_8X4X8FC,ar_5_8X4X12FC,ab_5_8X4X12FC,ar_1_2X4X8WB,ab_1_2X4X8WB,ar_5_8X4
X8WB,ab_5_8X4X8WB,ar_1_2X4X12CD
,ab_1_2X4X12CD,ar_1_2X4X9R,ab_1_2X4X9R,ar_1_2X54X8R,ab_1_2X54X8R,ar_1_2X54X12R,ab_1_2X54X12R,g_
1_2X4X8R,g_1_2X4X12R,g_5_8X4X8F
C,g_5_8X4X12FC,specialins) values (
'$cia',
'$job',
'$lote',
'$fecha',
'$plan',
'$direccion',
'$ciudad',
'$ar_1_2X4X8R',
'$ab_1_2X4X8R',
'$ar_1_2X4X12R',
'$ab_1_2X4X12R',
'$ar_5_8X4X8FC',
'$ab_5_8X4X8FC',
'$ar_5_8X4X12FC',
'$ab_5_8X4X12FC',
'$ar_1_2X4X8WB',
'$ab_1_2X4X8WB',
'$ar_5_8X4X8WB',
'$ab_5_8X4X8WB',
'$ar_1_2X4X12CD',
'$ab_1_2X4X12CD',
'$ar_1_2X4X9R',
'$ab_1_2X4X9R',
'$ar_1_2X54X8R',
'$ab_1_2X54X8R',
'$ar_1_2X54X12R',
'$ab_1_2X54X12R',
'$g_1_2X4X8R',
'$g_1_2X4X12R',
'$g_5_8X4X8FC',
'$ab_1_2X4X9R',
'$ar_1_2X54X8R',
'$ab_1_2X54X8R',
'$ar_1_2X54X12R',
'$ab_1_2X54X12R',
'$g_1_2X4X8R',
'$g_1_2X4X12R',
'$g_5_8X4X8FC',
'$g_5_8X4X12FC',
'$specialins')");
echo "Records actualizados: ";
mysql_affected_rows($link);

            mysql_close($link);

[Frederick]

You have quite a bit of information to insert and update, so that will cause a lot of delay. May want to try and restructure your information by using various tables to seperate the actions.

2nd : you do not have to use mysql_close($link) because you did not use mysql_pconnect().

[tasistro]

How much data is in your table (how many rows)?

Could be and index issue. Do you have a primary key, are lote and job keys? Also on the select change:

$result = mysql_query("Select * from aredry where lote='$lote' and job='$job'");

to

$result = mysql_query("Select lote from aredry where lote='$lote' and job='$job'");

Idea is that mysql only returns one field, just enough so that you know a record exists. This is more efficient than returning the whole record which could be a lot of data.

Insert should be no issue as MySQL is really fast even with a lot of data.

[Frederick]

I normally will designate a table as an index table. This table will store the primary keys of the other tables.
Examples:

I have an application that creates a table when a user enrolls for a test. The table created gets a name by the user ID and test ID (2_14) and these IDs are stored in a table called enrollment. To retrieve information on the users new table, the query needs to know what to look for.

The table that stores the information is

enrollment
id
aID
testID

So when i need to retrieve the information on the users test score, i reference the aID in the above table and now I can access the users test table and the users person information. This method is called an "Index Table". Stores the information for referencing other tables.
This allows you to seperate updates, inserts and so on and you do not have to update or insert all your information at the same time.

The more fields and data a pages has to search through causes delay time on your page load time. Breaking up that data reduces the load greatly. I have been commented many times about my applications having fast load times.

[tasistro]

Your dbase normalization actually increases CPU requirements. It is way quicker to do a lookup (update) on a single table (given it is properly indexed) than to join two or more tables or lookup an index on one table and then update on the other. I believe the problem with vmarellano is lack of indexes rather than too much data.

[vmarellano]

Hi and thanks for your comments.

I actually did not use any kind of indexing since I am not
aware of the best way to do it. Any suggestions?

Also, there are approximately 500 rows in the table and
"fecha" is the primary key. However, I believe the delay
has been present ever since I inserted record one.

Also, for my application, I do not do any updates, only inserts
and selects.

-Victor

[tasistro]

Read into this

http://www.mysql.com/doc/en/MySQL_Optimisation.html

try to issue this command on the dbase

CREATE INDEX job_lote ON aredry (job, lote);

Should create an index which will increase your SELECT speed.

[vmarellano]

Will indexing also increase my INSERT speed?

Thanks.

[tasistro]

doubt it, but I don't think you have an issue there. I think it is mostly the search part.

[BlueMeteor]

Originally posted by vmarellano
Will indexing also increase my INSERT speed?

Thanks.

not at all...
Sometimes Badindex will delay your inserts

agree with tasistro....copy and run your "insert" SQL in mysql command-mode

[vmarellano]

Hi,

Thanks to BlueMeteor, tasistro, and Frederick.

Thanks to you guys I am now a little more enlightened about
databases.

In any case, I have just realized why I was not able to write
to my database for a period of 1-2 minutes after the first
INSERT: my key field, "fecha" (or date in Spanish)
only included the hours and minutes. For this reason, once I
wrote a record, the clock had to advance by a period of time
enough to reach the next minute which could be anywhere
from 1 to 119 seconds. Therefore the delay.

Again, thanks to all!

[Sxooter]

IF you have foreign keys, the proper indexing WILL speed up inserts into tables with those keys.

There are too many old mysql hands around who aren't familiar with fks making comments here (not a slam, really, you're just not that familiar with fks and indexing.)

The reason proper indexing helps is that every insert into the subordinate table requires a lookup in the parent table. Imagine a parent table with 1M rows. which is faster, index scan or seq scan?

Of course the index scan, therefore, inserts into the subordinate table WILL be faster if the parent table is properly indexed.

TEST IT. You'll see I'm right.

A postgresql user...