Jump to content


Photo

A better way to do this query?


  • Please log in to reply
9 replies to this topic

#1 thedarkwinter

thedarkwinter
  • Members
  • PipPipPip
  • Advanced Member
  • 191 posts
  • LocationLondon

Posted 06 October 2006 - 09:04 AM

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:

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);

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
Remember - if you don't figure it out yourself, you'll probably forget it tomorrow :)

#2 Vikas Jayna

Vikas Jayna
  • Members
  • PipPipPip
  • Advanced Member
  • 121 posts
  • LocationNoida, Delhi, India

Posted 06 October 2006 - 10:35 AM

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
Vikas Jayna,
Project Manager - Jeevansathi.com
7 yrs. exp. in LAMP
Certified Mysql DBA
Zend Certified Engineer

#3 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 06 October 2006 - 10:47 AM

Something like Vikas Jayna 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!

#4 thedarkwinter

thedarkwinter
  • Members
  • PipPipPip
  • Advanced Member
  • 191 posts
  • LocationLondon

Posted 06 October 2006 - 11:27 AM

Hi

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

here is what i have:

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;
and histobjid int(9) squeezes in after histobj

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`)
)

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

LOCK TABLES `dd_obj` WRITE;
LOCK TABLES `dd_hist` WRITE;

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
Remember - if you don't figure it out yourself, you'll probably forget it tomorrow :)

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 October 2006 - 01:15 PM

Could you post the equivalent SELECT query with EXPLAIN?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 thedarkwinter

thedarkwinter
  • Members
  • PipPipPip
  • Advanced Member
  • 191 posts
  • LocationLondon

Posted 06 October 2006 - 01:26 PM

Hi

At the moment i use:

$objname in php = dd_obj.objname;

SELECT * FROM dd_hist WHERE hostobj='$objname';

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:
SELECT * FROM dd_hist WHERE histobjid='$objid';

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
Remember - if you don't figure it out yourself, you'll probably forget it tomorrow :)

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 October 2006 - 01:28 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 Vikas Jayna

Vikas Jayna
  • Members
  • PipPipPip
  • Advanced Member
  • 121 posts
  • LocationNoida, Delhi, India

Posted 11 October 2006 - 09:35 AM

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
Vikas Jayna,
Project Manager - Jeevansathi.com
7 yrs. exp. in LAMP
Certified Mysql DBA
Zend Certified Engineer

#9 thedarkwinter

thedarkwinter
  • Members
  • PipPipPip
  • Advanced Member
  • 191 posts
  • LocationLondon

Posted 11 October 2006 - 04:25 PM

Hi

Thanks guys...

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

Cheers,
tdw
Remember - if you don't figure it out yourself, you'll probably forget it tomorrow :)

#10 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 October 2006 - 07:58 PM

It _does_ matter... and what have you "done"?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users