SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2006
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Create smaller table from original table [mysql]

    I want to create new table from existing table without 'hour' separation so I would have less rows per date.
    is there a way to do it without involving a middle man script and just use SQL?

    my server is VPS with 4GB ram, 2 cores, data is 45+Million rows, running MariaDB 5.5.

    here is my current table scheme:
    Code:
    TableBig:
    id	bigint(20) unsigned Auto Increment	 
    folder_id	int(9) unsigned	 
    user_id	int(9) unsigned	 
    tag_id	int(11) unsigned	 
    tag_type	enum('m','t')	 
    date	date	 
    year	int(4) unsigned	 
    month	tinyint(2) unsigned	 
    day	tinyint(2) unsigned	 
    hour	tinyint(2) unsigned	 
    country	varchar(2)	 
    domain_id	int(10) unsigned	 
    url_id	bigint(20) unsigned	 
    unique_imprs	int(11) unsigned	 
    unique_actions	int(11) unsigned	 
    unique_clicks	int(11) unsigned	 
    gross_imprs	int(11) unsigned	 
    gross_clicks	int(11) unsigned	 
    gross_actions	int(11) unsigned	 
    transfer_bytes	bigint(20) unsigned
    here is my indexes:
    Code:
    Indexes
    PRIMARY	id
    INDEX	date, tag_id
    INDEX	date, folder_id
    INDEX	date, user_id
    my new table should look like this (Notice: no hour column):
    TableSmall:
    Code:
    id	bigint(20) unsigned Auto Increment	 
    folder_id	int(9) unsigned	 
    user_id	int(9) unsigned	 
    tag_id	int(11) unsigned	 
    tag_type	enum('m','t')	 
    date	date	 
    year	int(4) unsigned	 
    month	tinyint(2) unsigned	 
    day	tinyint(2) unsigned	 
    country	varchar(2)	 
    domain_id	int(10) unsigned	 
    url_id	bigint(20) unsigned	 
    unique_imprs	int(11) unsigned	 
    unique_actions	int(11) unsigned	 
    unique_clicks	int(11) unsigned	 
    gross_imprs	int(11) unsigned	 
    gross_clicks	int(11) unsigned	 
    gross_actions	int(11) unsigned	 
    transfer_bytes	bigint(20) unsigned
    Last edited by pamidor; Sep 23, 2013 at 22:59. Reason: typo

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    An INSERT INTO .... SELECT ... maybe?
    With a SUM() of the columns you want to sum, and a GROUP BY on those you don't want to sum.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •