List:General Discussion« Previous MessageNext Message »
From:Ronnie Sengupta Date:October 12 2004 6:11am
Subject:splitting a large table - does it improve performance?
View as plain text  
"Does splitting a large table (20 Million rows) growing at 5 million or more
a month into smaller tables improve performance given that the table can be
split in a logical way such that 95% queries don't need to look at data
spanning across the split tables"

 

 

Table Description:

 

20 Million Rows (increasing at 5 Million rows /month)

Table Size: 650 MB (Current)

Index Size: 600 MB (Current)

 


Field    

 Type                

 Null 

 Key 

 Default 

 Extra 


 id       

 bigint(20) unsigned 

      

 MUL 

0

       


 exercise 

 varchar(10)         

      

 MUL 

         

       


 score    

 tinyint(4)          

      

     

0

       


 date     

 Datetime            

 YES  

 MUL 

 NULL    

       

 

I need to perform about 200 select queries per second on this table with id,
exercise and date in where clause for different queries. This table is also
joined with another table having 1M rows in it.

 

System Description:

 

MySQL Master DB: Dedicated Master MySQL Database

Web Server 1: Load Balanced Web server with a slave MySQL DB

Web Server 2: Load Balanced Web server with a slave MySQL DB

 

All Select queries are executed on localhost by each web server. All
Insert/Update queries are executed on the Master MySQL database.

 


Thread
splitting a large table - does it improve performance?Ronnie Sengupta12 Oct
Re: splitting a large table - does it improve performance?SGreen12 Oct
  • Re: splitting a large table - does it improve performance?mos12 Oct
    • Re: splitting a large table - does it improve performance?Eric Bergen12 Oct