sangoku Posted February 21, 2010 Share Posted February 21, 2010 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? Link to comment https://forums.phpfreaks.com/topic/192837-what-is-bether/ Share on other sites More sharing options...
Mchl Posted February 21, 2010 Share Posted February 21, 2010 See 'vertical partitioning' section here http://en.wikipedia.org/wiki/Partition_%28database%29 for some clues. Link to comment https://forums.phpfreaks.com/topic/192837-what-is-bether/#findComment-1015756 Share on other sites More sharing options...
sangoku Posted February 21, 2010 Author Share Posted February 21, 2010 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???? Link to comment https://forums.phpfreaks.com/topic/192837-what-is-bether/#findComment-1015758 Share on other sites More sharing options...
Mchl Posted February 21, 2010 Share Posted February 21, 2010 Quote 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. Link to comment https://forums.phpfreaks.com/topic/192837-what-is-bether/#findComment-1015765 Share on other sites More sharing options...
sangoku Posted February 21, 2010 Author Share Posted February 21, 2010 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???? Link to comment https://forums.phpfreaks.com/topic/192837-what-is-bether/#findComment-1015769 Share on other sites More sharing options...
Mchl Posted February 21, 2010 Share Posted February 21, 2010 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. Link to comment https://forums.phpfreaks.com/topic/192837-what-is-bether/#findComment-1015773 Share on other sites More sharing options...
sangoku Posted February 21, 2010 Author Share Posted February 21, 2010 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. Link to comment https://forums.phpfreaks.com/topic/192837-what-is-bether/#findComment-1015778 Share on other sites More sharing options...
Mchl Posted February 21, 2010 Share Posted February 21, 2010 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. Link to comment https://forums.phpfreaks.com/topic/192837-what-is-bether/#findComment-1015801 Share on other sites More sharing options...
sangoku Posted February 21, 2010 Author Share Posted February 21, 2010 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 Link to comment https://forums.phpfreaks.com/topic/192837-what-is-bether/#findComment-1015810 Share on other sites More sharing options...
Mchl Posted February 21, 2010 Share Posted February 21, 2010 That might indicate that partitioning will be beneficial. You will not know for sure however, until you actually try. Link to comment https://forums.phpfreaks.com/topic/192837-what-is-bether/#findComment-1015813 Share on other sites More sharing options...
sangoku Posted February 21, 2010 Author Share Posted February 21, 2010 ye right create a 10 k test entrys... any idea how? Link to comment https://forums.phpfreaks.com/topic/192837-what-is-bether/#findComment-1015821 Share on other sites More sharing options...
Mchl Posted February 21, 2010 Share Posted February 21, 2010 Write a script to do it for you. Feed it with random numbers, lorem ipsum and what not. Link to comment https://forums.phpfreaks.com/topic/192837-what-is-bether/#findComment-1015826 Share on other sites More sharing options...
sangoku Posted February 21, 2010 Author Share Posted February 21, 2010 know that mean any suggestions is there any good testing script you can recommend? Link to comment https://forums.phpfreaks.com/topic/192837-what-is-bether/#findComment-1015831 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.