Optimizing MYSQL Statement

[Darghon]

Hello, I have a sql statement updates a table to add acquired materials to the database, upto a set max. Right now, this statement takes 27ms to update 1 record. So once the table if filled with thousands of records, this query will cause quite some problems, and this statement needs to be executed every 5 minutes.

 

This is the query:

UPDATE tbl_Materials a, tbl_MaterialsGain b 
SET a.Wood = LEAST( a.Wood + ( b.Wood * b.WoodBonus ) , b.Max ) ,
a.Clay = LEAST( a.Clay + ( b.Clay * b.ClayBonus ) , b.Max ) ,
a.Stone = LEAST( a.Stone + ( b.Stone * b.StoneBonus ) , b.max ) ,
a.Iron = LEAST( a.Iron + ( b.Iron * b.IronBonus ) , b.Max ) ,
a.Gold = ( a.Gold + ( b.Gold * b.GoldBonus ) ) 
WHERE a.TownID = b.TownID;

 

Basicly the query adds the gains to the materials table, unless the max allowed is less, then it's set to max.

ANY suggestions on optimizing this are very much appreciated

 

Oh, and I'm using mysql version 5.1.33

[fenway]

Show us the EXPLAIN output... as you need is a index on TownID.

[Darghon]

Explains =>

tbl_Materials

Field Type Null Key Default Extra

ID bigint(20) NO PRI NULL auto_increment

TownID bigint(20) NO   NULL

Wood bigint(20) YES   0

Clay bigint(20) YES   0

Stone bigint(20) YES   0

Iron bigint(20) YES   0

Gold bigint(20) YES   0

Record_Version bigint(20) YES   0

Delflag tinyint(1) YES   0

 

and tbl_MaterialsGain

Field Type Null Key Default Extra

ID bigint(20) NO PRI NULL auto_increment

TownID bigint(20) NO   NULL

Wood bigint(20) YES   0

Clay bigint(20) YES   0

Stone bigint(20) YES   0

Iron bigint(20) YES   0

Gold bigint(20) YES   0

WoodBonus decimal(6,2) YES   1.00

ClayBonus decimal(6,2) YES   1.00

StoneBonus decimal(6,2) YES   1.00

IronBonus decimal(6,2) YES   1.00

GoldBonus decimal(6,2) YES   1.00

Max bigint(20) YES   0

CivMax bigint(20) YES   0

Record_Version bigint(20) YES   0

Delflag tinyint(1) YES   0

[Mchl]

This is not EXPLAIN output.

See:

http://dev.mysql.com/doc/refman/5.1/en/explain.html

http://dev.mysql.com/doc/refman/5.1/en/using-explain.html

 

And hello... BIGINT for primary key? If you expect your tables to be THAT large, you'd better hire some professional DBA

[Darghon]

Never worked with the EXPLAIN command before, so don't shoot me down just yet.

 

I believe that this is the output you asked for:

Statement:

EXPLAIN EXTENDED SELECT *
FROM tbl_Materials, tbl_MaterialsGain
WHERE tbl_Materials.TownID = tbl_MaterialsGain.TownID

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	filtered 	Extra
1 	SIMPLE 	tbl_Materials 	ALL 	NULL 	NULL 	NULL 	NULL 	8 	100.00 	 
1 	SIMPLE 	tbl_MaterialsGain 	ALL 	NULL 	NULL 	NULL 	NULL 	8 	100.00 	Using where; Using join buffer

Made it to a select statement because a update statement isn't supported by Explain.

 

I made a generator that created my tables, and I'm not expecting to get that large amount of records in the database, I'm just prepared for it.

[Mchl]

You're not prepared. Unless you have a server farm Seriously, INT should be enough.

 

Anyway: start with creating an index on TownID as fenway suggested.

[Darghon]

I have added indexes on both TownID fields in each table, both a unique index, cause those tables have a 1 on 1 link with Town, (they are made to prevent the town table of having 50+ fields)

I might change my generation script to make int(11) instead of bigint(20), but it won't have that much of an impact on the system.

 

in meanwhile I have changed some of the surrounding code, and managed to get the procedure to 23 ms for 8 records, without noticeable change when records are added.

Made the executing script callable from php_cli in the crontab.

*/5  *  *  *  *  php /var/www/project/web/task.php tick

in which tick is the procedure containing the sql statement(s)