Mysql – INNER JOIN Giving time out on large database

MySQLperformanceupdate

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;

Best Answer

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.

Related Question