Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Getting time out on this script

UPDATE 
    uk_data AS ud
  INNER JOIN 
    uk_pc AS up 
        ON ud.cat10 = up.WardCode
SET 
    ud.cat8 = up.Latitude,
    ud.cat9 = up.Longitude;

uk_pc Table has 1,755,213 entries and uk_data has 24,510 entries.

Is there any other way to do this where I don't get time out?

I want to update uk_data cat8 and cat9 with uk_pc Latitude and Longitude.

Table1:  uk_pc
    Latitude
    Longitude
    WardCode

Table2:  uk_data
    cat8
    cat9
    cat10

in both tables WardCode and cat10 have same value.

Table Definitions

Table1 uk_data

CREATE TABLE IF NOT EXISTS `uk_data` (
  `slno` int(100) NOT NULL AUTO_INCREMENT,
  `comp_name` varchar(150) DEFAULT NULL,
  `comp_no` varchar(50) DEFAULT NULL,
  `comp_street` varchar(100) DEFAULT NULL,
  `comp_area` varchar(100) DEFAULT NULL,
  `comp_post_code` varchar(50) DEFAULT NULL,
  `comp_phone` varchar(100) DEFAULT NULL,
  `comp_phone1` varchar(100) DEFAULT NULL,
  `cat1` varchar(100) DEFAULT NULL,
  `cat2` varchar(100) DEFAULT NULL,
  `cat3` varchar(100) DEFAULT NULL,
  `cat4` varchar(100) DEFAULT NULL,
  `cat5` varchar(100) DEFAULT NULL,
  `cat6` varchar(100) DEFAULT NULL,
  `cat7` varchar(100) DEFAULT NULL,
  `cat8` varchar(100) DEFAULT NULL,
  `cat9` varchar(100) DEFAULT NULL,
  `cat10` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`slno`),
  UNIQUE KEY `Phone` (`comp_phone`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=31717 ;

Table2(uk_pc)

    DROP TABLE IF EXISTS `uk_pc`;
CREATE TABLE `uk_pc` (
  `slno` int(11) NOT NULL AUTO_INCREMENT,
  `Postcode` varchar(25) DEFAULT NULL,
  `Latitude
Latitude` decimal(9,6) DEFAULT NULL,
  `Longitude` decimal(9,6) DEFAULT NULL,
  `Easting` varchar(25) DEFAULT NULL,
  `Northing` varchar(25) DEFAULT NULL,
  `GridRef` varchar(25) DEFAULT NULL,
  `County` varchar(25) DEFAULT NULL,
  `District` varchar(25) DEFAULT NULL,
  `Ward` varchar(25) DEFAULT NULL,
  `DistrictCode` varchar(25) DEFAULT NULL,
  `WardCode` varchar(15) DEFAULT NULL,
  `Country` varchar(25) DEFAULT NULL,
  `CountyCode` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`slno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
share|improve this question
2  
please paste the explain plan –  Sashi Kant Dec 30 '12 at 18:10
    
@SashiKant error? on server it say loading for very long time and nothing is coming out and if i try to output through browser it gives time out –  Harinder Dec 30 '12 at 18:12
    
he means do an explain query. explain select * from uk_data AS ud INNER JOIN uk_pc AS up ON ud.cat10 = up.WardCode WHERE (ud.cat10 = up.WardCode) and up.Latitude > 0; –  Ryan Naddy Dec 30 '12 at 18:14
3  
Think about using indices. –  shark555 Dec 30 '12 at 18:16
1  
You know there are other datatypes besides VARCHAR, more suitable for storing numbers with decimals, that Latitude and Longitude probably have ? –  ypercube Dec 31 '12 at 15:01

3 Answers 3

up vote 7 down vote accepted

At the very least, you should have an index on WardCode on uk_pc. If you also add Latitude and Longitude it will become covering for this query and so the table will not need to be used at all.

Your datatypes aren't really the most efficient, so I'd definitely revisit your table designs. narrower columns (Latitude and Longitude are better off as decimal and would never be 100 characters long) will store better in the tables (and of course in the indexes as well). The width of varchar columns might seem to be something you don't neet to worry about, but it does affect what the database engine sets aside while performing queries since it does not know if it will encounter larger strings in the data. In general, limiting the working set like this can only help, especially when it is easily foreseeable.

share|improve this answer
    
Thx But i try editing my table still same Problem ... :( –  Harinder Dec 31 '12 at 17:09
    
Thx bro it worked afer i made index on both tables ;) CREATE INDEX cat10 ON uk_data(cat10); and similarily CREATE INDEX WarCode ON uk_pc(WarCode); thx –  Harinder Jan 1 '13 at 2:01

Try something like this:

UPDATE uk_data  ud, uk_pc up 
SET ud.cat8 = up.Latitude, ud.cat9 = up.Longitude
WHERE ud.cat10 = up.WardCode
share|improve this answer
    
thx this work on small database ... but i have big database and it is giving same error ... just loading and if i stop it after an hour my database get corrupted ;( –  Harinder Dec 31 '12 at 5:18
    
@Harinder, you actually havea teeny tiny database. A big database would have more like a billion records. –  HLGEM Jul 16 '13 at 18:19

@didierc Gave suggestion to make common fields as index so I did and same script work very fast

CREATE INDEX cat10 ON uk_data(cat10); 
CREATE INDEX WarCode ON uk_pc(WarCode);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.