I hope you will help me to find a solution for my startup project. From the beginning it became very huge …
I have a huge table with about ~200M rows.
id int(11) NOT NULL auto_increment,
id1 int(11) default NULL,
id2 int(11) default NULL,
num1 smallint(5) NOT NULL,
num2 decimal(11,2) default NULL,
num3 decimal(7,2) default NULL, PRIMARY KEY (
id), UNIQUE KEY
Whole table is ~4G, idexes - ~10G
During quite simple query (I have more complicated with group and 1 join to 20M table) server performance very low. For example, query:
select count(tp.id), sum(tp.num1), sum(tp.num2)fromtable tpwhereid1 = 187085
which are returns 20K rows (ya - I need all of them) are runs for about 22 secs. As you can see it’s absolutely unacceptable.
Do you think it has a sense to tune my dedicated server or I need to redesign my database structure?
My dedicated box:
Win Server 2003
1G of RAM
Do you need me to attach SHOW GLOBAL STATUS here?
Thank you in advance.