1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

DBA told me to do this but did not say why or its advantages? Can someone help

Discussion in 'Databases' 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 ;



    He says to do this:
    Recommendations:
    SEMrush
    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 ;



    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?
     
    Brad77, Nov 20, 2013 IP
    SEMrush
  2. khodem

    khodem Member

    Messages:
    201
    Likes Received:
    5
    Best Answers:
    3
    Trophy Points:
    45
    #2
    there is alot of question in one question here I will try to answer if I miss anything let me know:

    Change lng and lat to DECIMAL(11, 8) or the desired precision.
    - because this is the correct datatype for lat and long
    If not required, remove the UNIQUE KEY idx_name, idx_name2, idx_name3
    - he told if you don't need this remove it, he just suggested if you don't need something then don't use it, as if you don't need it there is pressure on database.

    Change to InnoDB
    - is better than MyISAM and kinda standard depending in organisation and bellow SQL he given is just doing that
    (ALTER TABLE reviews ENGINE=InnoDB)

    Should change rating_id to 'id', just for programming standard
    - this is helpful when you code an application it's faster and easy to remember while coding and it's standard for developers

    Add index on rating_user_id
    - index will make your query faster to respond and it might be that in your organisation or developer using that column most often and index improve the out put of those SQL query and solution he given is (CREATE INDEX by_user ON rating_user_id(rating_user_id);

    hope I could helped.
    cheers :D
     
    khodem, Dec 8, 2013 IP
  3. HalfDedi

    HalfDedi Greenhorn

    Messages:
    30
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    8
    #3
    basically, mixing database engine is not a good idea
     
    HalfDedi, Jan 31, 2014 IP
  4. Aeromir

    Aeromir Active Member

    Messages:
    9
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    56
    #4
    If your lat/lon data is decimal, you can do calculations with it like using great circle calcs find all records within __ distance of a specific lat/lon without having to cast the data into a numeric format.
    Sorting will be correct using a numeric data type vs varchar.
     
    Aeromir, Apr 3, 2014 IP