| mysql performance [message #1579] |
Wed, 08 August 2007 02:37  |
 adorearun
Messages: 1
Registered: August 2007 |
Junior Member |
|
|
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
|
|
|
| Re: mysql performance [message #1592 is a reply to message #1579 ] |
Thu, 16 August 2007 08:04  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
Members
Search
Help
Register
Login
Home
