D

[ Video Sharing CMS v4 ] wrong datatypes in database

Started by dtiberio,

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Go Down Pages1

dtiberioTopic starter

Last Edit: by Alexander
almost all of the columns in the database are using the wrong datatypes.

you want the datatypes to be as small as possible. this makes the database smaller, and the searches return less empty space, and it makes everything quicker. and it uses less disk space.

according to this, you allow billions of different gender choices. there are only male, female, and unselected, so you should use tinyint instead of int(11):
Code Select 
| gender          | int(11)      | NO   |     | NULL                |                |

if a password can only be 20 characters long for example, then it should be 'varchar(20)', not mediumtext:
| password        | mediumtext   | NO   |     | NULL                |                |

same with username, use varchar(20):
Code Select 
username        | varchar(255) | NO   | UNI | NULL                |                |

if there are only 3 media types, it should be a tinyint (allows 255 numbers) not int (billions of media types)
Code Select 
| media       | int(11)      | NO   |     | 1       |                |

this is how to change the media type to a tinyint:
alter table vibe_videos change media media tinyint unsigned not null;
and then you end up with:
Code Select 
| media       | tinyint(3) unsigned | NO   |     | NULL    |                |

much smaller and faster.

ALL OF YOUR INT COLUMNS SHOULD BE "UNSIGNED" because you are never going to use negative numbers.
  •  

dtiberioTopic starter

#1
this allows for 65,000 channels instead of the 2 billion you have it set for right now:
alter table vibe_videos change category category smallint unsigned not null;
  •  

dtiberioTopic starter

#2
Last Edit: by Alexander
this allows for 65,000 maximum users:
Code Select 
alter table vibe_videos change user_id user_id smallint unsigned not null;

this allows for 16,000,000 users:
Code Select 
alter table vibe_videos change user_id user_id mediumint unsigned not null;

you want to use the smallest amount you can deal with, it increases performance for joins, selects, deletes, indexes, everything.
  •  

dtiberioTopic starter

#3
it is almost always better to use varchar instead of text, blob, or anything else.

a text type is a fixed size. if it is empty, it still takes up a lot of space, more than any other type of data.

if a varchar is empty, it is like being only 1 character long. a text type could easily be 1,000 characters long. so it is better to use varchar as much as possible.
  •  

dtiberioTopic starter

#4
use this command to fix the auto_increment columns:

alter table vibe_videos change id id int unsigned not null auto_increment;

leave out the 'primary key' command, because that is an index and will not be changed.
  •  

dtiberioTopic starter

#5
Last Edit: by Alexander
this is what my table finally looks like in its optimized form:
Code Select 
mysql> desc vibe_videos;
+-------------+-----------------------+------+-----+---------+----------------+
| Field       | Type                  | Null | Key | Default | Extra          |
+-------------+-----------------------+------+-----+---------+----------------+
| id          | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| media       | tinyint(3) unsigned   | NO   |     | NULL    |                |
| token       | varchar(255)          | NO   |     | NULL    |                |
| pub         | tinyint(3) unsigned   | NO   | MUL | NULL    |                |
| user_id     | smallint(5) unsigned  | NO   |     | NULL    |                |
| date        | varchar(255)          | NO   |     | NULL    |                |
| featured    | tinyint(3) unsigned   | NO   |     | NULL    |                |
| source      | varchar(255)          | NO   | UNI | NULL    |                |
| tmp_source  | varchar(255)          | NO   | MUL | NULL    |                |
| title       | varchar(300)          | NO   |     | NULL    |                |
| thumb       | varchar(255)          | NO   |     | NULL    |                |
| duration    | mediumint(8) unsigned | NO   |     | NULL    |                |
| description | varchar(1000)         | NO   |     | NULL    |                |
| tags        | varchar(500)          | NO   |     | NULL    |                |
| category    | smallint(5) unsigned  | NO   |     | NULL    |                |
| views       | mediumint(8) unsigned | NO   | MUL | NULL    |                |
| liked       | smallint(5) unsigned  | NO   |     | NULL    |                |
| disliked    | smallint(5) unsigned  | NO   |     | NULL    |                |
| nsfw        | int(10) unsigned      | NO   |     | NULL    |                |
| embed       | varchar(1000)         | NO   |     | NULL    |                |
| remote      | varchar(1000)         | NO   |     | NULL    |                |
| srt         | varchar(1000)         | NO   |     | NULL    |                |
| privacy     | tinyint(3) unsigned   | NO   |     | NULL    |                |
+-------------+-----------------------+------+-----+---------+----------------+
23 rows in set (0.00 sec)

  •  

dtiberioTopic starter

#6
Last Edit: by Alexander
use the max command to find the largest value:
Code Select 
mysql> select max(sub) from vibe_channels;
+----------+
| max(sub) |
+----------+
|        1 |


since sub has a max value of 1, it should be a tinyint unsigned which ranges from 0 to 255 and is faster.
not int, which ranges from -2,000,000,000 to 2,000,000,000.
  •  

dtiberioTopic starter

#7
Last Edit: by Alexander
Code Select 
mysql> desc vibe_ads;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| ad_id      | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |


bigint unsigned = 18,446,744,073,709,551,615 possible ads

ad_id should be tinyint unsigned for 255 ads, or smallint unsigned for up to 65,000 ads.
  •  

dtiberioTopic starter

#8
Last Edit: by Alexander
Code Select 
mysql> desc vibe_likes;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| uid   | int(11)      | NO   | MUL | NULL    |                |
| vid   | varchar(200) | NO   |     | NULL    |                |


'vid' should not be a varchar, it should be some form of unsigned integer, I am assuming that the vid is always a number and never alphabetic characters
  •  

dtiberioTopic starter

#9
Last Edit: by Alexander
copy everything below and put it into a file such as phpvibe.sql.

then enter (along with any usernames and password, also assumes db is named phpvibe):
mysql phpvibe < phpvibe.sql

and it will create a new database using the datatypes I picked, with no data, all from scratch. note that i did not redo the datatypes for every table, just the ones that had data.

Code Select 
-- MySQL dump 10.13  Distrib 5.1.73, for redhat-linux-gnu (x86_64)
--
-- Host: localhost    Database: phpvibe
-- ------------------------------------------------------
-- Server version	5.1.73

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `vibe_activity`
--

DROP TABLE IF EXISTS `vibe_activity`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_activity` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user` int(10) unsigned NOT NULL,
  `type` int(11) NOT NULL,
  `object` int(11) NOT NULL,
  `extra` mediumtext NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=267 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_ads`
--

DROP TABLE IF EXISTS `vibe_ads`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_ads` (
  `ad_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `ad_spot` varchar(64) NOT NULL DEFAULT '',
  `ad_type` varchar(64) NOT NULL DEFAULT '0',
  `ad_content` varchar(1000) NOT NULL,
  `ad_title` varchar(64) NOT NULL,
  `ad_pos` varchar(64) NOT NULL,
  PRIMARY KEY (`ad_id`),
  KEY `ad_type_idx` (`ad_type`),
  KEY `ad_spot_idx` (`ad_spot`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_channels`
--

DROP TABLE IF EXISTS `vibe_channels`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_channels` (
  `cat_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `child_of` smallint(5) unsigned NOT NULL,
  `picture` varchar(150) NOT NULL,
  `cat_name` varchar(150) CHARACTER SET utf8 COLLATE utf8_swedish_ci NOT NULL,
  `cat_desc` varchar(500) CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT NULL,
  `type` tinyint(3) unsigned NOT NULL,
  `sub` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`cat_id`),
  UNIQUE KEY `cat_uni` (`child_of`,`cat_name`)
) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_crons`
--

DROP TABLE IF EXISTS `vibe_crons`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_crons` (
  `cron_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `cron_type` varchar(500) NOT NULL,
  `cron_name` varchar(64) NOT NULL DEFAULT '',
  `cron_period` mediumint(8) unsigned NOT NULL,
  `cron_pages` tinyint(3) unsigned NOT NULL,
  `cron_lastrun` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `cron_value` varchar(255) NOT NULL,
  PRIMARY KEY (`cron_id`),
  KEY `cron_type_idx` (`cron_type`(333))
) ENGINE=MyISAM AUTO_INCREMENT=129 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_em_comments`
--

DROP TABLE IF EXISTS `vibe_em_comments`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_em_comments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `object_id` varchar(64) NOT NULL,
  `created` varchar(50) NOT NULL,
  `sender_id` varchar(128) DEFAULT NULL,
  `comment_text` text,
  `admin_reply` enum('0','1') NOT NULL DEFAULT '0',
  `rating_cache` int(11) NOT NULL DEFAULT '0',
  `access_key` varchar(100) DEFAULT NULL,
  `visible` enum('0','1') NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `object_id` (`object_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_em_likes`
--

DROP TABLE IF EXISTS `vibe_em_likes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_em_likes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `comment_id` int(10) unsigned NOT NULL,
  `sender_ip` bigint(20) NOT NULL,
  `vote` enum('1','-1') NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `comment_id` (`comment_id`),
  KEY `sender_ip` (`sender_ip`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_homepage`
--

DROP TABLE IF EXISTS `vibe_homepage`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_homepage` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `ord` tinyint(3) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `type` varchar(200) CHARACTER SET utf8 COLLATE utf8_swedish_ci NOT NULL,
  `ident` varchar(200) CHARACTER SET utf8 COLLATE utf8_swedish_ci NOT NULL,
  `querystring` varchar(200) CHARACTER SET utf8 COLLATE utf8_swedish_ci NOT NULL,
  `total` tinyint(3) unsigned NOT NULL,
  `mtype` tinyint(3) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_langs`
--

DROP TABLE IF EXISTS `vibe_langs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_langs` (
  `lang_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `term` varchar(1000) NOT NULL,
  PRIMARY KEY (`lang_id`)
) ENGINE=MyISAM AUTO_INCREMENT=260 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_languages`
--

DROP TABLE IF EXISTS `vibe_languages`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_languages` (
  `term_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `lang_name` varchar(204) NOT NULL DEFAULT '',
  `lang_code` varchar(64) NOT NULL DEFAULT '',
  `lang_terms` longtext NOT NULL,
  PRIMARY KEY (`term_id`),
  UNIQUE KEY `lang_code` (`lang_code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_likes`
--

DROP TABLE IF EXISTS `vibe_likes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_likes` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `uid` smallint(5) unsigned NOT NULL,
  `vid` int(10) unsigned NOT NULL,
  `type` varchar(200) CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid_uni` (`uid`,`vid`)
) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_options`
--

DROP TABLE IF EXISTS `vibe_options`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_options` (
  `option_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(64) NOT NULL DEFAULT '',
  `option_value` varchar(1000) NOT NULL,
  `autoload` varchar(20) NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`),
  UNIQUE KEY `option_name_uni` (`option_name`)
) ENGINE=MyISAM AUTO_INCREMENT=80 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_pages`
--

DROP TABLE IF EXISTS `vibe_pages`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_pages` (
  `pid` int(11) NOT NULL AUTO_INCREMENT,
  `menu` int(11) NOT NULL DEFAULT '0',
  `date` text COLLATE utf8_swedish_ci NOT NULL,
  `title` varchar(300) COLLATE utf8_swedish_ci NOT NULL,
  `pic` longtext COLLATE utf8_swedish_ci NOT NULL,
  `content` longtext COLLATE utf8_swedish_ci NOT NULL,
  `tags` varchar(500) COLLATE utf8_swedish_ci NOT NULL,
  PRIMARY KEY (`pid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_playlist_data`
--

DROP TABLE IF EXISTS `vibe_playlist_data`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_playlist_data` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `playlist` smallint(5) unsigned NOT NULL,
  `video_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `playlist_idx` (`playlist`)
) ENGINE=MyISAM AUTO_INCREMENT=55 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_playlists`
--

DROP TABLE IF EXISTS `vibe_playlists`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_playlists` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `owner` smallint(5) unsigned NOT NULL,
  `picture` varchar(150) NOT NULL,
  `title` varchar(150) CHARACTER SET utf8 COLLATE utf8_swedish_ci NOT NULL,
  `description` varchar(500) CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT NULL,
  `views` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_postcats`
--

DROP TABLE IF EXISTS `vibe_postcats`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_postcats` (
  `cat_id` int(11) NOT NULL AUTO_INCREMENT,
  `picture` varchar(150) NOT NULL,
  `cat_name` varchar(150) CHARACTER SET utf8 COLLATE utf8_swedish_ci NOT NULL,
  `cat_desc` varchar(500) CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT NULL,
  PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_posts`
--

DROP TABLE IF EXISTS `vibe_posts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_posts` (
  `pid` int(11) NOT NULL AUTO_INCREMENT,
  `ch` int(11) NOT NULL DEFAULT '1',
  `date` text COLLATE utf8_swedish_ci NOT NULL,
  `title` varchar(300) COLLATE utf8_swedish_ci NOT NULL,
  `pic` longtext COLLATE utf8_swedish_ci NOT NULL,
  `content` longtext COLLATE utf8_swedish_ci NOT NULL,
  `tags` varchar(500) COLLATE utf8_swedish_ci NOT NULL,
  PRIMARY KEY (`pid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_reports`
--

DROP TABLE IF EXISTS `vibe_reports`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_reports` (
  `r_id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `vid` varchar(200) CHARACTER SET utf8 COLLATE utf8_swedish_ci NOT NULL,
  `reason` longtext CHARACTER SET utf8 COLLATE utf8_swedish_ci,
  `motive` longtext NOT NULL,
  PRIMARY KEY (`r_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_tags`
--

DROP TABLE IF EXISTS `vibe_tags`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_tags` (
  `tagid` int(11) NOT NULL AUTO_INCREMENT,
  `tag` varchar(50) COLLATE utf8_swedish_ci NOT NULL,
  `tcount` int(11) NOT NULL,
  PRIMARY KEY (`tagid`),
  KEY `tag` (`tag`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_users`
--

DROP TABLE IF EXISTS `vibe_users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_users` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `pass` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `lastlogin` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `group_id` varchar(255) COLLATE utf8_swedish_ci NOT NULL DEFAULT '4',
  `avatar` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `cover` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `date_registered` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `name` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `username` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `type` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `gid` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `fid` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `oauth_token` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `local` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `country` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `bio` varchar(1000) COLLATE utf8_swedish_ci NOT NULL,
  `views` mediumint(8) unsigned NOT NULL,
  `fblink` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `twlink` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `glink` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `gender` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_uni` (`email`),
  UNIQUE KEY `username_uni` (`username`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_users_friends`
--

DROP TABLE IF EXISTS `vibe_users_friends`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_users_friends` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `uid` smallint(5) unsigned NOT NULL,
  `fid` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `uid_idx` (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_users_groups`
--

DROP TABLE IF EXISTS `vibe_users_groups`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_users_groups` (
  `id` int(16) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `admin` tinyint(1) NOT NULL,
  `default_value` tinyint(1) NOT NULL,
  `access_level` bigint(32) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_videos`
--

DROP TABLE IF EXISTS `vibe_videos`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_videos` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `media` tinyint(3) unsigned NOT NULL,
  `token` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `pub` tinyint(3) unsigned NOT NULL,
  `user_id` smallint(5) unsigned NOT NULL,
  `date` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `featured` tinyint(3) unsigned NOT NULL,
  `source` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `tmp_source` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `title` varchar(300) COLLATE utf8_swedish_ci NOT NULL,
  `thumb` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `duration` mediumint(8) unsigned NOT NULL,
  `description` varchar(1000) COLLATE utf8_swedish_ci NOT NULL,
  `tags` varchar(500) COLLATE utf8_swedish_ci NOT NULL,
  `category` smallint(5) unsigned NOT NULL,
  `views` mediumint(8) unsigned NOT NULL,
  `liked` smallint(5) unsigned NOT NULL,
  `disliked` smallint(5) unsigned NOT NULL,
  `nsfw` int(10) unsigned NOT NULL,
  `embed` varchar(1000) COLLATE utf8_swedish_ci NOT NULL,
  `remote` varchar(1000) COLLATE utf8_swedish_ci NOT NULL,
  `srt` varchar(1000) COLLATE utf8_swedish_ci NOT NULL,
  `privacy` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `source_uni` (`source`),
  KEY `views_idx` (`views`),
  KEY `pub_idx` (`pub`),
  KEY `source_idx` (`source`),
  KEY `tmp_source_idx` (`tmp_source`)
) ENGINE=MyISAM AUTO_INCREMENT=12299 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vibe_videos_tmp`
--

DROP TABLE IF EXISTS `vibe_videos_tmp`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vibe_videos_tmp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `name` varchar(500) NOT NULL,
  `path` mediumtext CHARACTER SET utf8 COLLATE utf8_swedish_ci,
  `ext` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2014-05-13 21:37:44
  •  

PHPVibe A.

#10
If you learn to use the code tag when posting, I will personally nominate you for "client of the year" :)
Thanks for feedback and solutions, I've forwarded all of them.
Go Up Pages1

Similar topics (7)