DBA says to do this but does not tell me why!!

Discussion in 'MySQL' started by Brad77, Nov 20, 2013.

  1. #1
    This is my present table:

    CREATE TABLE IF NOT EXISTS `geocode_us` (
    
      `id` int(11) NOT NULL AUTO_INCREMENT,
    
      `loc_b` varchar(32) DEFAULT NULL,
    
      `loc_a` varchar(32) DEFAULT NULL,
    
      `lat` varchar(16) DEFAULT NULL,
    
      `lng` varchar(16) DEFAULT NULL,
    
      PRIMARY KEY (`id`),
    
      UNIQUE KEY `idx_name` (`loc_a`,`loc_b`,`lat`,`lng`),
    
      UNIQUE KEY `idx_name2` (`loc_a`,`loc_b`),
    
      UNIQUE KEY `idx_name3` (`lat`,`lng`)
    
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=81814 ;
    Code (markup):


    He says to do this:
    Recommendations:

    Change lng and lat to DECIMAL(11, 8) or the desired precision.

    If not required, remove the UNIQUE KEY idx_name, idx_name2, idx_name3

    He did not say why or what this will do by changing this?



    Also this:

    This is my table:

    CREATE TABLE IF NOT EXISTS `reviews` (
    
      `rating_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The id of the rating',
    
      `rating_vendor_id` int(11) NOT NULL COMMENT 'The id of the page to be rated',
    
      `rating_user_id` int(11) NOT NULL COMMENT 'If a user is signed in, get their id',
    
      `rating` int(11) NOT NULL COMMENT 'The rating for the page',
    
      `rating_comment` longtext NOT NULL COMMENT 'The title of the page to be rated',
    
      `review_time` varchar(60) DEFAULT NULL,
    
      `review_title` varchar(124) DEFAULT NULL,
    
      `used_vendor` int(5) DEFAULT NULL,
    
      `reply` longtext,
    
      PRIMARY KEY (`rating_id`)
    
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
    Code (markup):


    He suggest this:
    Recommendations:

    Change to InnoDB

    Should change rating_id to 'id', just for programming standard

    Add index on rating_user_id

    ALTER TABLE reviews ENGINE=InnoDB;

    CREATE INDEX by_user ON rating_user_id(rating_user_id);



    Why should I do this and what will it do or improve?

    Can anyone chime in on this?
     
    Last edited by a moderator: Nov 21, 2013
    Brad77, Nov 20, 2013 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,830
    Likes Received:
    4,541
    Best Answers:
    123
    Trophy Points:
    665
    #2
    Indexes are fantastic things but if you don't use them right they can and will slow your database down.

    Having an index on 3 columns can give huge savings on query time but if you never use the index then it's creating an overhead that impacts on every insert & update for no gain.

    To find out if it's being used copy each query you run and paste it into the MySQL tool you use - there should be an "explain" option which will show the indexes being used. If that long one never gets used then drop it.

    I agree with the rating_id change to id - that's normal for the primary key and obviously fits in with the naming standard in the rest of the database.

    Foreign keys should all be indexed so add the index for rating_user_id

    I don't have any particular knowledge about the engine type but my understanding is that InnoDB is the current best practice.
     
    sarahk, Nov 21, 2013 IP