GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

mysql performance

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • mysql performance

    Hi All,
    I have a mysql DB which has 32 mill rows...myisam tables and mysql version is 4.0.20.
    The table in which i have 32 mill rows is minmax table where we store info of all attributes the schema is given below.

    "ARCHIVEDTIME" BIGINT,
    "RESID" integer not null,
    "DURATION" integer not null,
    "ATTRIBUTEID" integer not null,
    "MINVALUE" BIGINT,
    "MAXVALUE" BIGINT,
    "TOTAL" BIGINT,
    "TOTALCOUNT" BIGINT,
    PRIMARY KEY ("ARCHIVEDTIME","RESID","DURATION","ATTRIBUTEID ")

    Now we decided to split this 32 mill rows and put in new tables according to attribute id column. for that i use

    insert into newTable select * from minmax where attributeid =708

    .but this query takes too much time .....for tis particular attributeid 708 there are 5002047 rows.Also in minmax table attributeid column is indexed.
    HOW do i improve the performance of the splitting the table

    Thanks n advance
    Arun

  • #2
    What is EXPLAIN for such SELECT query alone ?

    In this case as a lot of rows match full table scan would be best (so you can use IGNORE KEY to force it)

    For insert part - make sure you insert into the empty table and your myisam_sort_buffer_size and myisam_max_sort_file_size are large enough.

    Comment

    Working...
    X