A better way to do this query?

[thedarkwinter]

Hi

Okay, so you know when you are lazy and do something you know you will have to change one day... that one day is now!

Ive got a live system running (clients logging in 24/7) that requires a database change.

Here is the query i need to run to update it:

[code]
ALTER TABLE dd_hist ADD COLUMN histobjid int(9) AFTER histobj;
UPDATE dd_hist SET dd_hist.histobjid = (SELECT dd_obj.objid FROM dd_obj WHERE dd_obj.objname=dd_hist.histobj);
[/code]

Basically, as you can see i've been associating a history table to objects by using a string value... now we need to be able to change the name of the object, and still associate the history (i.e. those ID numbers i should have just used in the first place!) Which is easy to do, the problem comes in that there is currently about 20 000 rows in dd_obj, and 30 000 in dd_hist. Not all of the rows in dd_hist are associated to objects, so in this case the value of dd_hist.hostobj is NULL - and in some cases the the object itself has been removed, but the history still has a value associated to it.

This query works, and just doesn't update the dd_hist.histobjid when it doesn't find a value. When i ran the query on my test system (An AMD64 3200) it took 45 mins. The live server is not as high spec - and i need to minimize the downtime as much as possible.

Can anyone see a better, quicker way to do this?
I have a bunch of other updates to do at the same time and i dont want to leave a "temporarily down for updates" page for too long.

Thanks in advance.
tdw

[Vikas Jayna]

Try this query:

UPDATE dd_hist, dd_obj SET dd_hist.histobjid = dd_obj.objid WHERE dd_obj.objname=dd_hist.histobj;

and see if this works! Ideally you should have an index on at least one of the two columns histobj and objname to make this query faster

[printf]

Something like [b]Vikas Jayna[/b] said would work, but I would add a CASE in there, because you don't know if the obj is there, and it will let you set a different value if you want. Plus 45 minutes seem really long, could you post your scheme because some thing isn't right with that time. I would also set a LOCK when your doing this, it makes things over all faster!


me!

[thedarkwinter]

Hi

thanks "Vikas Jayna", just finished running that query now but i didn't go any faster

here is what i have:

[code]CREATE TABLE `dd_hist` (
  `histid` int(6) NOT NULL auto_increment,
  `histobj` varchar(50) default NULL,
  `histusr` varchar(50) default NULL,
  `histip` varchar(50) default NULL,
  `histinquery` text,
  `histoutquery` text,
  `histdt` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `histdesc` text,
  PRIMARY KEY  (`histid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
[/code]
and histobjid int(9) squeezes in after histobj

[code]CREATE TABLE `dd_obj` (
  `objid` int(8) NOT NULL auto_increment,
  `objname` varchar(255) NOT NULL default '',
  `altname` varchar(255) default NULL,
  `altlang` varchar(255) default NULL,
  `clid` int(6) default NULL,
  `divid` int(6) NOT NULL default '0',
  `objstat` int(2) default '0',
  `objmanstat` int(2) default '7',
  `ponumber` varchar(50) default NULL,
  `ordered` timestamp NOT NULL default '0000-00-00 00:00:00',
  `created` timestamp NOT NULL default '0000-00-00 00:00:00',
  `usrid` varchar(50) default NULL,
  `passwd` varchar(10) default NULL,
  `psid` int(6) NOT NULL default '0',
  `psid2` int(6) default '0',
  `psid3` int(6) default '0',
  `psid4` int(6) default '0',
  `comments` mediumtext,
  `commentsint` mediumtext,
  `user_def1` varchar(255) default NULL,
  `user_def2` varchar(255) default NULL,
  `user_def3` varchar(255) default NULL,
  `user_def4` varchar(255) default NULL,
  `user_def5` varchar(255) default NULL,
  PRIMARY KEY  (`objid`)
)[/code]

objname in the above is never going to be duplicate (governed by php) and never null (mysql NOT NULL)

histobj in dd_hist.histobj could be NULL as it might not be referring to dd_obj.

my mySQL knowledge isn't great so i'm not sure how to use CASE...

for LOCK would i

[code]
LOCK TABLES `dd_obj` WRITE;
LOCK TABLES `dd_hist` WRITE;
[/code]

before running the query?

Its not the end of the world - if it takes time thats fine, i just thought i would try and shave it down!!


cheers,
tdw

[fenway]

Could you post the equivalent SELECT query with EXPLAIN?

[thedarkwinter]

Hi

At the moment i use:

$objname in php = dd_obj.objname;

[code]SELECT * FROM dd_hist WHERE hostobj='$objname';[/code]

this selects any item from the history table that is related to $objname. there could be any amount in there (including 0)

i need to get the same information now, but using an ID rather than the object name - so that if the object name is changed, then the id will still match.

so now i have

dd_hist
histid    histobj    data
1          apple      created
2          apple      eaten
3          orange    created
4          apple      thrown away

and i need to have

histid    histobj      hostobjid      data
1          apple        1                  created
2          apple        1                  eaten
3          orange      2                  created
4          apple        1                  thrown away
5          NULL          NULL          some other irrelevant history thing happened
6          applejuice  1                  renamed from apple
7          orangepie  2                  renamed from orange

so:
[code]SELECT * FROM dd_hist WHERE histobjid='$objid';[/code]

So everything is set up to do this... i just need to update the database to match up the ID numbers before i can allow for the renaming of objects.

Is that at all helpful?

thanks
tdw

[fenway]

Take the UPDATE query, change it to a SELECT query, run EXPLAIN, and post the output so we can see what the database is up to.

[Vikas Jayna]

Hi,

As i said you need to have an index on at least one of the two columns histobj and objname to make the join query  faster. None of the two indexes are there in the database schema that you gave. I'm sure the query will run much faster after creating the indexes. Try the query again after making the index.

Regards,
Vikas Jayna

[thedarkwinter]

Hi

Thanks guys...

I've already done it so it doesn't matter anymore!

Cheers,
tdw

[fenway]

It _does_ matter... and what have you "done"?