What is bether?

[sangoku]

Hy guys i have a bit of a dilema here i am making a MySQL table  and i have 2 tables.

 

CREATE  TABLE IF NOT EXISTS `Forum`.`user_hash` (
  `user_ID` INT(10) UNSIGNED NOT NULL ,
  `username` VARCHAR(45) NOT NULL ,
  `user_is_online` TINYINT(1) NOT NULL ,
  `password_hash` VARCHAR(40) NOT NULL ,
  `usergroup_id` SMALLINT(5) UNSIGNED NOT NULL ,
  `avatar_loc` VARCHAR(250) NULL ,
  `energy` INT(7) UNSIGNED NOT NULL DEFAULT 26 ,
  `reputation` INT(7) UNSIGNED NOT NULL DEFAULT 1 ,
  `Broj_odgovora` INT( UNSIGNED NOT NULL DEFAULT 0 ,
  `zadnji_login_u` TIMESTAMP NOT NULL ,
  `user_tema` INT(3) NOT NULL ,
  `pocetna_stranica` INT( UNSIGNED NOT NULL DEFAULT 1 ,
  `downloadovao_kolicina` INT(10) UNSIGNED NOT NULL ,
  `broj_downlaoda` INT(10) UNSIGNED NOT NULL ,
  PRIMARY KEY (`user_ID`) ,
  INDEX `fk_user_id` (`user_ID` ASC) ,
  INDEX `fk_CSS_tema` (`user_tema` ASC) ,
  INDEX `username` (`username` ASC, `user_ID` ASC) ,
  INDEX `pocetna_stranica` (`pocetna_stranica` ASC) ,
  INDEX `usergroup` (`usergroup_id` ASC) ,
  CONSTRAINT `fk_user_id`
    FOREIGN KEY (`user_ID` )
    REFERENCES `Forum`.`user_data` (`user_ID` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_CSS_tema`
    FOREIGN KEY (`user_tema` )
    REFERENCES `Forum`.`CSS_stil` (`ID_stila` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `pocetna_stranica`
    FOREIGN KEY (`pocetna_stranica` )
    REFERENCES `Forum`.`meni_stranice` (`ID_stranice` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `usergroup`
    FOREIGN KEY (`usergroup_id` )
    REFERENCES `Forum`.`usergroup` (`usergroup_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

 

and another one

CREATE  TABLE IF NOT EXISTS `Forum`.`user_data` (
  `user_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `dan_registracije` DATE NOT NULL ,
  `registarcioni_IP` INT(12) UNSIGNED NOT NULL ,
  `locked_ip` BINARY NOT NULL DEFAULT 0 ,
  `banovan` TINYINT(1) NOT NULL DEFAULT false ,
  `GMT_time` INT(2) UNSIGNED NOT NULL ,
  `mail` VARCHAR(120) NOT NULL ,
  `pol_korisnika` TINYINT(1) NOT NULL COMMENT 'true je male, false female XD' ,
  `msn` VARCHAR(120) NULL ,
  `yahoo` VARCHAR(120) NULL ,
  `alm` VARCHAR(120) NULL ,
  `ICQ` VARCHAR(120) NULL ,
  `facebook` VARCHAR(120) NULL ,
  `gmail` VARCHAR(120) NULL ,
  `homepage` VARCHAR(240) NULL ,
  `show_mail` TINYINT(1) NULL DEFAULT false ,
  `show_other_mails` TINYINT(1) NULL DEFAULT false ,
  PRIMARY KEY (`user_ID`) ,
  INDEX `mail` (`mail` ASC) )
ENGINE = InnoDB

 

The names are on Serbian but that duos not mater. I am in a  dilemma here, is it good practice to keep the data that is by its nature big and not regular like alternate user data which is in the second statement in a separate table and the usually called and often accessed.

My question is. Is it  better to keep those data in a single table or, in separate ones?

[Mchl]

See 'vertical partitioning' section here http://en.wikipedia.org/wiki/Partition_%28database%29 for some clues.

[sangoku]

Lols....  I knew about horizontal partitioning but not about this weirdness of  a thing...

 

I use horizontal already but the thing is it duos not support FK constrains... is it the same with the vertical????

[Mchl]

I use horizontal already but the thing is it duos not support FK constrains... is it the same with the vertical????

 

Horizontal partitioning in MySQL does support FK constraints (fro InnoDB tables), and so does vertical.

[sangoku]

as far i know it dous not, but maybe the damn tool is out of date il check it later manually i use MySQL workbranch....

 

Can you recomand any bether????

[Mchl]

I assume you're talking about horizontal partitioning as it is available since MySQL 5.1

http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

 

there's no reason they wouldn't support FK constraints.

[sangoku]

Yes, but when i try to reference the table with partitioning, the MySQL work branch says it cant do it so i belived it....

 

I mean when a table references a partitioned table to be exact.

[Mchl]

Anyway, there's no easy answer to question: should you use vertical partitioning or not. It all depends on how often do you need each column AND how large are columns that you don't need on regular basis.

[sangoku]

Well the main problem is that that table is called very very often  but most of the time it is called in that way that most of the table data is not needed (by viewing of the user) and that table will be very big. like around 100k of entrys

[Mchl]

That might indicate that partitioning will be beneficial. You will not know for sure however, until you actually try.

[sangoku]

ye right create a 10 k test entrys... any idea how?

[Mchl]

Write a script to do it for you. Feed it with random numbers, lorem ipsum and what not.

[sangoku]

know that mean any suggestions is there any good testing script you can recommend?