Jump to content

A better way to do this query?


thedarkwinter

Recommended Posts

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
Link to comment
Share on other sites

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!
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.