mysql performance

MySQL & MariaDB Other MySQL® Questions
1

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.