How can a split a big table ?

Discussion in 'MySQL' started by Kemal, Jun 25, 2006.

0
  1. #1
    Hi everyboy,
    i have problem in table structure. I have table which has 3 million records and i want to seperate this table intto another tables.
    For example 0 to 100,000 records in another tablo 100,000 to 200,000 records in another table.
     
    Kemal, Jun 25, 2006 IP
  2. designcodes

    designcodes Well-Known Member

    Messages:
    213
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    133
    #2
    copy table, delete top 33%
     
    designcodes, Jun 26, 2006 IP
  3. jnestor

    jnestor Peon

    Messages:
    133
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Don't. Unless there's a reason for why they should be in separate tables other than the number of rows.

    As long you have have an index on the right columns it really shouldn't be a problem.
     
    jnestor, Jun 29, 2006 IP
  4. muaythai

    muaythai Peon

    Messages:
    92
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #4
    It depends on the database type if it's oracle you can partition tables, but 3 million rows are not that much.
     
    muaythai, Jun 30, 2006 IP
  5. Kemal

    Kemal Peon

    Messages:
    20
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #5
    MYsql

    Tables structure is

    CREATE TABLE IPCountry
    (
    ipFROM INT(10) UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
    ipTO INT(10) UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
    countrySHORT CHAR(2) NOT NULL,
    countryLONG VARCHAR(45) NOT NULL,
    ipISP VARCHAR(255) NOT NULL,
    PRIMARY KEY(ipFROM, ipTO)
    );
     
    Kemal, Jul 2, 2006 IP
  6. muaythai

    muaythai Peon

    Messages:
    92
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Maybe MySQLs MERGE can solve your problems?

    I cannot add a link but search for it in the MySQL Reference manual

    Some issues it can solve:

    Obtain more speed. You can split a big read-only table based on some criteria, and then put individual tables on different disks. A MERGE table on this could be much faster than using the big table.

    Perform more efficient searches. If you know exactly what you are looking for, you can search in just one of the split tables for some queries and use a MERGE table for others. You can even have many different MERGE tables that use overlapping sets of tables.
     
    muaythai, Jul 3, 2006 IP
  7. Kemal

    Kemal Peon

    Messages:
    20
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #7
    i solved this problem by using "LIMIT"
    Like that,
    Insert into data1 select * from IPISP LIMIT 0,100000
    Insert into data2 select * from IPISP LIMIT 100000,100000
    Insert into data3 select * from IPISP LIMIT 200000,100000
    Insert into data4 select * from IPISP LIMIT 300000,100000
    Insert into data5 select * from IPISP LIMIT 400000,100000
    Insert into data6 select * from IPISP LIMIT 500000,100000
    Insert into data7 select * from IPISP LIMIT 600000,100000
    Insert into data8 select * from IPISP LIMIT 700000,100000
    Insert into data9 select * from IPISP LIMIT 800000,100000
    Insert into data10 select * from IPISP LIMIT 900000,100000
    Insert into data11 select * from IPISP LIMIT 1000000,100000
    Insert into data12 select * from IPISP LIMIT 1100000,100000
    Insert into data13 select * from IPISP LIMIT 1200000,100000
    Insert into data14 select * from IPISP LIMIT 1300000,100000
    ...

    Maybe it helps to other people
     
    Kemal, Aug 2, 2006 IP