Tables
CREATE TABLE `mob_db` (
`ID` MEDIUMINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Sprite` TEXT COLLATE utf8_general_ci NOT NULL,
`kName` TEXT COLLATE utf8_general_ci NOT NULL,
`iName` TEXT COLLATE utf8_general_ci NOT NULL,
`LV` TINYINT(6) UNSIGNED NOT NULL DEFAULT '0',
`HP` INTEGER(9) UNSIGNED NOT NULL DEFAULT '0',
`SP` MEDIUMINT(9) UNSIGNED NOT NULL DEFAULT '0',
`EXP` MEDIUMINT(9) UNSIGNED NOT NULL DEFAULT '0',
`JEXP` MEDIUMINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Range1` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
`ATK1` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',
`ATK2` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',
`DEF` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',
`MDEF` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',
`STR` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
`AGI` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
`VIT` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
`INT` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
`DEX` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
`LUK` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
`Range2` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
`Range3` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
`Scale` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
`Race` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
`Element` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
`Mode` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',
`Speed` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',
`aDelay` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',
`aMotion` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',
`dMotion` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',
`MEXP` MEDIUMINT(9) UNSIGNED NOT NULL DEFAULT '0',
`ExpPer` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`MVP1id` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`MVP1per` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`MVP2id` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`MVP2per` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`MVP3id` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`MVP3per` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Drop1id` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Drop1per` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Drop2id` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Drop2per` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Drop3id` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Drop3per` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Drop4id` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Drop4per` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Drop5id` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Drop5per` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Drop6id` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Drop6per` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Drop7id` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Drop7per` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Drop8id` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Drop8per` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Drop9id` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`Drop9per` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`DropCardid` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
`DropCardper` SMALLINT(9) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`)
)ENGINE=MyISAM
CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE TABLE `inventory` (
`id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`char_id` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
`nameid` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
`amount` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
`equip` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
`identify` SMALLINT(6) NOT NULL DEFAULT '0',
`refine` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`attribute` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
`card0` SMALLINT(11) NOT NULL DEFAULT '0',
`card1` SMALLINT(11) NOT NULL DEFAULT '0',
`card2` SMALLINT(11) NOT NULL DEFAULT '0',
`card3` SMALLINT(11) NOT NULL DEFAULT '0',
`expire_time` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `char_id` (`char_id`)
)ENGINE=MyISAM
AUTO_INCREMENT=4985843 ROW_FORMAT=FIXED CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE TABLE `cart_inventory` (
`id` INTEGER(11) NOT NULL AUTO_INCREMENT,
`char_id` INTEGER(11) NOT NULL DEFAULT '0',
`nameid` INTEGER(11) NOT NULL DEFAULT '0',
`amount` INTEGER(11) NOT NULL DEFAULT '0',
`equip` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
`identify` SMALLINT(6) NOT NULL DEFAULT '0',
`refine` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`attribute` TINYINT(4) NOT NULL DEFAULT '0',
`card0` INTEGER(11) NOT NULL DEFAULT '0',
`card1` INTEGER(11) NOT NULL DEFAULT '0',
`card2` INTEGER(11) NOT NULL DEFAULT '0',
`card3` INTEGER(11) NOT NULL DEFAULT '0',
`expire_time` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `char_id` (`char_id`)
)ENGINE=MyISAM
AUTO_INCREMENT=92648 ROW_FORMAT=FIXED CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE TABLE `storage` (
`id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`account_id` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
`nameid` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
`amount` SMALLINT(11) UNSIGNED NOT NULL DEFAULT '0',
`equip` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
`identify` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',
`refine` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`attribute` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
`card0` SMALLINT(11) NOT NULL DEFAULT '0',
`card1` SMALLINT(11) NOT NULL DEFAULT '0',
`card2` SMALLINT(11) NOT NULL DEFAULT '0',
`card3` SMALLINT(11) NOT NULL DEFAULT '0',
`expire_time` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `account_id` (`account_id`)
)ENGINE=MyISAM
AUTO_INCREMENT=916165 ROW_FORMAT=FIXED CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE TABLE `guild_storage` (
`id` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`guild_id` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
`nameid` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
`amount` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
`equip` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
`identify` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',
`refine` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`attribute` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
`card0` SMALLINT(11) NOT NULL DEFAULT '0',
`card1` SMALLINT(11) NOT NULL DEFAULT '0',
`card2` SMALLINT(11) NOT NULL DEFAULT '0',
`card3` SMALLINT(11) NOT NULL DEFAULT '0',
`expire_time` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `guild_id` (`guild_id`)
)ENGINE=MyISAM
AUTO_INCREMENT=299 ROW_FORMAT=FIXED CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
Table status
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation
mob_db MyISAM 10 Dynamic 937 104 98 220 281474976710655 11 264 0 null 18.02.2009 10:21 18.02.2009 11:21 null utf8_general_ci null
cart_inventory MyISAM 10 Fixed 9 553 44 420 332 12384898975268863 238 592 0 92 648 18.02.2009 11:10 30.03.2009 17:56 30.03.2009 17:55 utf8_general_ci
guild_storage MyISAM 10 Fixed 72 36 2 592 10133099161583615 3 072 0 299 18.02.2009 11:10 30.03.2009 18:26 null utf8_general_ci
inventory MyISAM 10 Fixed 101 742 36 3 662 712 10133099161583615 2 466 816 0 4 985 843 18.02.2009 11:10 30.03.2009 18:06 30.03.2009 18:06 utf8_general_ci
storage MyISAM 10 Fixed 132 317 34 4 498 778 9570149208162303 3 236 864 0 916 165 18.02.2009 11:10 30.03.2009 18:10 30.03.2009 18:10 utf8_general_ci
Explain
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY mob_db ALL null null null null 937 Using where
5 DEPENDENT SUBQUERY cart_inventory ALL null null null null 9 553 Using where
4 DEPENDENT SUBQUERY guild_storage ALL null null null null 72 Using where
3 DEPENDENT SUBQUERY inventory ALL null null null null 101 742 Using where
2 DEPENDENT SUBQUERY storage ALL null null null null 132 317 Using where
Unfortunately i can not change database structure. And i still can not write right query with JOIN 8(
Edited 1 time(s). Last edit at 03/31/2009 04:01AM by Allary Max.