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?
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.